Is there a CHOOSE function? Need to calculate quarter based on date

Marina Iida

Recently, my manager and I tried to find a formula that would calculate the FY quarter based on a "Start Date" column. We couldn't use some of the functions we saw on the community because our fiscal year starts in February. We finally found a couple of solutions that work using the IFERROR formula, but it's quite long!

Curious to hear if anyone has a more elegant solution? Or do you know if Smartsheet has anything similar to the "CHOOSE" function in Excel? Here's an article that I found that has a great solution using the "CHOOSE" function if your fiscal year starts on a month other than Jan., but I can't find anything like that in SS so far. https://www.myonlinetraininghub.com/excel-convert-dates-to-fiscal-quarters-and-years

Here's the examples of the functions we are using now:

=IFERROR(IF(MONTH([Start Date]@row) = 1, "Q4", IF(MONTH([Start Date]@row) = 2, "Q1", IF(MONTH([Start Date]@row) = 3, "Q1", IF(MONTH([Start Date]@row) = 4, "Q1", IF(MONTH([Start Date]@row) = 5, "Q2", IF(MONTH([Start Date]@row) = 6, "Q2", IF(MONTH([Start Date]@row) = 7, "Q2", IF(MONTH([Start Date]@row) = 8, "Q3", IF(MONTH([Start Date]@row) = 9, "Q3", IF(MONTH([Start Date]@row) = 10, "Q3", IF(MONTH([Start Date]@row) = 11, "Q4", IF(MONTH([Start Date]@row) = 12, "Q4")))))))))))), "")

=IFERROR(IF(OR(MONTH([Start Date]@row) = 2, MONTH([Start Date]@row) = 3, MONTH([Start Date]@row) = 4), "Q1", IF(OR(MONTH([Start Date]@row) = 5, MONTH([Start Date]@row) = 6, MONTH([Start Date]@row) = 7), "Q2", IF(OR(MONTH([Start Date]@row) = 8, MONTH([Start Date]@row) = 9, MONTH([Start Date]@row) = 10), "Q3", IF(OR(MONTH([Start Date]@row) = 11, MONTH([Start Date]@row) = 12, MONTH([Start Date]@row) = 1), "Q4")))), "")

Thanks!

Paul Newcome

You could try something like this...

=IFERROR("Q" + IF(OR(MONTH([Start Date]@row) = 1, MONTH([Start Date]@row) >= 11), "4", IF(MONTH([Start Date]@row) >= 8, "3", IF(MONTH([Start Date]@row) >= 5, "2", "1"))), "")