months to quarter - odd FY

mikemuller

I'm trying to create a formula to associate months with a quarter - and it's not standard fiscal quarter. so I was thinking of doing an If/or and clearly identifying which months, but I can't figure out syntax.. any help appreciated - or any other ideas?

Paul Newcome

What are the months for each quarter?

mikemuller

Sept, Oct, Nov = Q1

Dec, Jan, Feb = Q2

and so on

Paul Newcome

Try this:

="Q" + IF(OR(MONTH([Date Column]@row) <= 2, MONTH([Date Column]@row) = 12), "2", IF(MONTH([Date Column]@row) <= 5, "3", IF(MONTH([Date Column]@row) <= 8, "4", "1")))

mikemuller

Thank you for your help, but I have another complication/clarifier. I have 2 columns for the 'date' listing the month and year separately as the forecast date may span years. So, I have 'month' listed as text (January, February, etc) and year listed (FY22 or FY23) [see below]. I could change the year to just read 2022 or 2023 and we can change the output of the formula to read FY of that works... thoughts?

mikemuller

actually, I just added (and hid) a date column that I used as a reference point. To be fully transparent for folks that might use this, then I changed the [date column] part of the formula to just date@row as the title of the column was just "date". All set. Thank you!

Paul Newcome

Happy to help. 👍️