How to output FY statements based on Dates

Marc Bills

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!

Kelly Moore

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

Kelly Moore

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

Marc Bills

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!