How to output FY statements based on Dates
Hey so I'm trying to translate a formula from Excel to smartsheets and I'm struggling to figure out the best way to go about it. I'll provide an example of the data and the formula from excel to give as much detail as I can if anyone would possibly be able to help me. Thank you in advance!
I have a column of retirement dates that go back as far as 2002 and as far as 2070, this column is called Retirement Eligible (K2). Essentially, the way this data gets broken down is all persons are marked in the excel sheet as being part of a certain FY retirement group -
"Currently" - Yesterday and backward
"FY23" - Today to 9/30/2023
"FY24-25" - 10/1/2023 - 9/30/2025
"FY26-27" - 10/1/2025 - 9/30/2027
"FY28-FY29" - 10/1/2027 - 9/30/29
"FY30+" - <1/1/2099
Below is the formula that spits out the results for said designations, its really just a subsequent checker.
=IF(K2<DATE(2023,1,23),"Currently",IF(K2<DATE(2023,10,1),"FY23",IF(K2<DATE(2025,10,1),"FY24-25",IF(K2<DATE(2027,10,1),"FY26-27",IF(K2<DATE(2029,10,1),"FY28-29",IF(K2<DATE(2099,1,1),"FY30+"))))))
Is there something in Smartsheets that can output something similar against date ranges like this with just basic text output?
For whatever its worth, I've done some checking around the forums here and found some range output formula's and I was able to get close I think, but I get the "Incorrect Argument" output. Here was my formula -
=IFERROR(AND([Retirement Elig]@row > DATE(1900, 10, 1), [Retirement Elig]@row < DATE(TODAY()), "Currently", IF(AND([Retirement Elig]@row > DATE(TODAY(), [Retirement Elig]@row < DATE(2023, 9, 30)), "FY23", IF(AND([Retirement Elig]@row > DATE(2023, 10, 1), [Retirement Elig]@row < DATE(2025, 9, 30)), "FY24-25", IF(AND([Retirement Elig]@row > DATE(2025, 10, 1), [Retirement Elig]@row < DATE(2027, 9, 30)), "FY25-27", IF(AND([Retirement Elig]@row > DATE(2027, 10, 1), [Retirement Elig]@row < DATE(2029, 9, 30)), "FY27-29", IF(AND([Retirement Elig]@row > DATE(2029, 10, 1), [Retirement Elig]@row < DATE(2099, 9, 30)), "FY30+", "0"))))))))
Any help is very appreciated!
Best Answer
-
Hey @Marc Bills
A few parentheses were out of place. Also, the function TODAY() doesn't require the DATE function. The other syntax error was trying to use an IFERROR with an AND function.
=IF(AND([Retirement Elig]@row > DATE(1900, 10, 1), [Retirement Elig]@row < TODAY()), "Currently", IF(AND([Retirement Elig]@row > TODAY(), [Retirement Elig]@row < DATE(2023, 9, 30)), "FY23", IF(AND([Retirement Elig]@row > DATE(2023, 10, 1), [Retirement Elig]@row < DATE(2025, 9, 30)), "FY24-25", IF(AND([Retirement Elig]@row > DATE(2025, 10, 1), [Retirement Elig]@row < DATE(2027, 9, 30)), "FY25-27", IF(AND([Retirement Elig]@row > DATE(2027, 10, 1), [Retirement Elig]@row < DATE(2029, 9, 30)), "FY27-29", IF(AND([Retirement Elig]@row > DATE(2029, 10, 1), [Retirement Elig]@row < DATE(2099, 9, 30)), "FY30+", 0))))))
Will this formula work for you?
Kelly
Answers
-
Hey @Marc Bills
A few parentheses were out of place. Also, the function TODAY() doesn't require the DATE function. The other syntax error was trying to use an IFERROR with an AND function.
=IF(AND([Retirement Elig]@row > DATE(1900, 10, 1), [Retirement Elig]@row < TODAY()), "Currently", IF(AND([Retirement Elig]@row > TODAY(), [Retirement Elig]@row < DATE(2023, 9, 30)), "FY23", IF(AND([Retirement Elig]@row > DATE(2023, 10, 1), [Retirement Elig]@row < DATE(2025, 9, 30)), "FY24-25", IF(AND([Retirement Elig]@row > DATE(2025, 10, 1), [Retirement Elig]@row < DATE(2027, 9, 30)), "FY25-27", IF(AND([Retirement Elig]@row > DATE(2027, 10, 1), [Retirement Elig]@row < DATE(2029, 9, 30)), "FY27-29", IF(AND([Retirement Elig]@row > DATE(2029, 10, 1), [Retirement Elig]@row < DATE(2099, 9, 30)), "FY30+", 0))))))
Will this formula work for you?
Kelly
-
This worked perfectly, thank you Kelly! For any future person that reads this I actually still hit the 'Invalid Operation' error, until I converted the 'Retirement Elig' column into dates which I had forgotten to do, as it was still in Text mode.
Again thanks so much for your help in getting me to the right formula!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!