2

I am trying to create a calculated column that looks at the Date1 column and pulls only the year into the new column. If the Date1 column is blank, then bring in the Date2 column. If Date2 column is blank, then display "Review".

If Date1 or Date2 is in 1st quarter (Jan-Mar), then I need the year to display the previous year. (example 2/15/2024 should display 2023 in the calculated column).

I have started with this formula

=IF(ISBLANK([Date1]),"Review",TEXT([Date1],"yyyy"))

How can I modify this to include the additional requirements of looking at Date2 column and the 1st quarter?

Desired output

Date1 Date2 Year (calculated column)
5/13/2024 2024
2/14/2024 2023
7/11/2024 2024
Review

1 Answer 1

3

Try using formula like below for your SharePoint calculated column (Year):

=IF(ISBLANK([Date1]),IF(ISBLANK([Date2]),"Review",IF(OR(MONTH([Date2])=1,MONTH([Date2])=2,MONTH([Date2])=3),YEAR([Date2])-1,YEAR([Date2]))),IF(OR(MONTH([Date1])=1,MONTH([Date1])=2,MONTH([Date1])=3),YEAR([Date1])-1,YEAR([Date1])))

Output:

enter image description here

Notes:

  1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  2. Use correct display name of your SharePoint columns in above formula.
  3. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

Update from comments:

Try using calculated column formula like below to remove commas from calculated column results:

=IF(ISBLANK([Date1]),IF(ISBLANK([Date2]),"Review",IF(OR(MONTH([Date2])=1,MONTH([Date2])=2,MONTH([Date2])=3),TEXT(YEAR([Date2])-1,"####"),TEXT(YEAR([Date2]),"####"))),IF(OR(MONTH([Date1])=1,MONTH([Date1])=2,MONTH([Date1])=3),TEXT(YEAR([Date1])-1,"####"),TEXT(YEAR([Date1]),"####")))
6
  • Thanks so much Ganesh! The calculations are working as expected! The only problem is I have commas in my Calculated Year column. Field is Single line of Text, not sure why the comma is being added. Any additional tips?
    – kendallyn
    Nov 17 at 5:10
  • @ganesh-sanap-mvp I was able to revise the formatting for most of the calculations to remove the commas in the year but for the items that fall in Q1 and are taking the previous year, the commas are still present. Any advice?
    – kendallyn
    Nov 17 at 16:33
  • Hi @kendallyn, can you show us the updated/current formula you are using which removes comma from some of the years? Nov 18 at 13:10
  • Try using the formula given in updated answer above. Nov 18 at 13:24
  • 1
    The updated formula you provided solved my problem!! Thanks again for your time and expertise, it is much appreciated!
    – kendallyn
    Nov 21 at 5:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.