Refer to a date and return the fiscal year

Melitta

Hello,

Looking for some assistance with a formula that refers to a column (DOS) and depending upon the date range it falls into, it returns the fiscal year.

I'm using IF and I'm getting #unparsable so I don't have it written properly.

=IF(DOS@row > DATE(2022,6,30), “FY22”, “X”)

Paul Newcome

I actually think this may provide better scaling:

="FY" + RIGHT(YEAR(DOS@row) + IF(MONTH(DOS@row)>= 7, 1, 0), 2)

Carson Penticuff

Your double quotes do not appear to be the "standard" double quote character. Try this:

=IF(DOS@row > DATE(2022,6,30), "FY22", "X")

Paul Newcome

The slanted quotes are called "Smart Quotes" which (ironically enough) are not recognized as valid characters in a Smartsheet formula. You will need to retype them either here in the Community, directly in Smartsheet, or in a text editor such as Notepad (not Word).

Melitta

Thank you both. I corrected the issue with the quotation marks.

I have 2 follow-up questions. How do I get it to check within a range? and add in FY23?

=IF(AND(DOS@row) <= DATE(2021, 6, 30), >=DATE(2022, 7, 1), "FY22"), (DOS@row) <= DATE(2022, 6, 30), >=DATE(2023,7,1), "FY23")

ker9

Hi @Melitta

Please see if this will work for you. The "DOS" column should be setup as a Date column.

=IF(DOS@row < DATE(2021, 7, 1), "FY21", IF(DOS@row < DATE(2022, 7, 1), "FY22", IF(DOS@row < DATE(2023, 7, 1), "FY23")))

Hope this helps!

Paul Newcome

I actually think this may provide better scaling:

="FY" + RIGHT(YEAR(DOS@row) + IF(MONTH(DOS@row)>= 7, 1, 0), 2)