How to create a calculated column to pull the year of Date column 1, and if blank, pull year from Date column 2, if that's also blank then put Hold

Question

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

Answer 1

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:

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]),"####")))