calculate quarter end for corporate fiscal year

mcollbarth

Hello! I would like to calculate quarters based on our fiscal calendar, which starts in November. I wrote the following formula to determine quarter and year. It works for all quarters except for Q1. For dates in Q1, the formula is just returning "Q1" and not the year.

Quarter Formula.PNG

Any assistance would be greatly appreciated.

="Q" + IF(OR(MONTH(Start@row) = 11, MONTH(Start@row) = 12, MONTH(Start@row) = 1), 1, IF(OR(MONTH(Start@row) = 2, MONTH(Start@row) = 3, MONTH(Start@row) = 4), 2, IF(OR(MONTH(Start@row) = 5, MONTH(Start@row) = 6, MONTH(Start@row) = 7), 3, IF(OR(MONTH(Start@row) = 8, MONTH(Start@row) = 9, MONTH(Start@row) = 10), 4))) + " " + YEAR(Start@row))

Mike TV

@mcollbarth

Nevermind. I forgot you said your quarter starts in November. Lemme work on this.

Mike TV

@mcollbarth

="Q" + IF(MONTH(Start@row) = 1, 1 + " " + YEAR(Start@row), IF(OR(MONTH(Start@row) = 2, MONTH(Start@row) = 3, MONTH(Start@row) = 4), 2 + " " + YEAR(Start@row), IF(OR(MONTH(Start@row) = 5, MONTH(Start@row) = 6, MONTH(Start@row) = 7), 3 + " " + YEAR(Start@row), IF(OR(MONTH(Start@row) = 8, MONTH(Start@row) = 9, MONTH(Start@row) = 10), 4 + " " + YEAR(Start@row), IF(OR(MONTH(Start@row) = 11, MONTH(Start@row) = 12), 1 + " " + (YEAR(Start@row) + 1))))))

mcollbarth

Thanks Mike. I removed the "year" calculation from the nested month calculation and it seems to be working!