Refer to a date and return the fiscal year
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”)
Best Answer
-
I actually think this may provide better scaling:
="FY" + RIGHT(YEAR(DOS@row) + IF(MONTH(DOS@row)>= 7, 1, 0), 2)
Answers
-
Your double quotes do not appear to be the "standard" double quote character. Try this:
=IF(DOS@row > DATE(2022,6,30), "FY22", "X")
-
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).
-
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")
-
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!
-
I actually think this may provide better scaling:
="FY" + RIGHT(YEAR(DOS@row) + IF(MONTH(DOS@row)>= 7, 1, 0), 2)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!