Is there are "CHOOSE" function? Calculating quarter from dates

Marina Iida

My manager and I have been trying to find a formula that would calculate the FY quarter based on the "Start Date". We haven't been able to use a lot of the formulas we found on the Community because our fiscal year starts in Feb. instead of Jan. We've found a couple of solutions that's working for us now, but it's really long!

I'm curious if anyone has a more elegant solution? or knows of a formula that works like a "CHOOSE" function in Excel? I found an article that has a solution regardless of what month the fiscal year starts - https://www.myonlinetraininghub.com/excel-convert-dates-to-fiscal-quarters-and-years . But I haven't found anything like the CHOOSE function in SS.

Thanks for any suggestions!

This is the solution that we're 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")))), "")

Leibel S

@Marina Iida

I think the below should work for you:

=IFERROR("Q" + IF(MONTH([Start Date]@row) = 1, 4, ROUNDUP((MONTH([Start Date]@row) - 1) / 3, 0)), "")

Leibel S

@Marina Iida

I think the below should work for you:

=IFERROR("Q" + IF(MONTH([Start Date]@row) = 1, 4, ROUNDUP((MONTH([Start Date]@row) - 1) / 3, 0)), "")

Marina Iida

That's brilliant! Thank you - it worked perfectly.

Sandra Wlasiewicz

Hi @Leibel S

Can you help me get the same but including the year? So it displays Q3 2023?

Leibel S

@Sandra Wlasiewicz

the below would add the year.

=IFERROR("Q" + IF(MONTH([Start Date]@row) = 1, 4 + " " + YEAR([Start Date]@row - 1), ROUNDUP((MONTH([Start Date]@row) - 1) / 3, 0) + " " + YEAR([Start Date]@row)), "")

So anything in January 2023 will show "Q4 2022"

Sandra Wlasiewicz

@Leibel S thank you so much!!!

Don't want to be a burden but I couldn't find if Smartsheet could do what you just sent me but for a range of dates? I mean I have a start and end date column and I wish to generate quarter and year for the range the dates cover. E.g. Start date 6/1/2023, End Date 9/3/2023 and the result would be Q2 2023 and Q3 2023 in one column. I saw similar stuff based on INDEX and a reference table but maybe you know how to fit that command in one formula? :)

Appreciate it!

Leibel S

You want in a multi select column?