# calculate quarter end for corporate fiscal year

Options

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.

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))

• ✭✭✭✭✭✭
edited 07/12/22
Options

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

• ✭✭✭✭✭✭
Options

="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))))))

• Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!