calculate quarter end for corporate fiscal year

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

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    edited 07/12/22

    @mcollbarth

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

  • Mike TV
    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))))))


  • 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!