Formula to calculate fiscal quarter when quarter starts on February

Colleen Jones

Hello, I'm very new to SmartSheet. I found a discussion from 2020 about doing a calculation for fiscal quarter and including year, but I cannot quite get it right. Our FY starts in February and ends in January of the next year. I'm trying to show the quarter and year as Q1FY24.

Here's is the formula based on one created by someone else. I can get the year and Q1 is now correct, but I can't get the quarters to show correctly for any other period:

="Q" + IF(MONTH([Planned Finish]@row) >= 2, "1FY", IF(MONTH([Planned Finish]@row) >= 11, "4FY", IF(MONTH([Planned Finish]@row) >= 8, "3FY", "2FY"))) + IF(MONTH([Planned Finish]@row) >= 2, RIGHT(INT(YEAR([Planned Finish]@row) + 1), 2), RIGHT(YEAR([Planned Finish]@row), 2))

Any tips on how to correct this would be greatly appreciated. I'm so close!

Paul Newcome

Try this instead...

="Q" + IF(OR(MONTH(Planned Finish]@row) = 1, MONTH([Planned Finish]@row)>= 11), "4", IF(MONTH([Planned Finish]@row)<= 4, "1", IF(MONTH([Planned Finish]@row)<= 7, "2", "3"))) + "FY" + RIGHT(YEAR([Planned Finish]@row) - IF(MONTH([Planned Finish]@row) = 1, 1, 0), 2)

Colleen Jones

Thanks so much, Paul!

There was one square bracket missing at the start, but it worked when I added that. I was still getting the wrong FY years, so I changed the last section from 1, 1, 0 to 1, 0, -1. Not totally sure what I'm doing, but it is now working.

="Q" + IF(OR(MONTH([Planned Finish]@row) = 1, MONTH([Planned Finish]@row) >= 11), "4", IF(MONTH([Planned Finish]@row) <= 4, "1", IF(MONTH([Planned Finish]@row) <= 7, "2", "3"))) + "FY" + RIGHT(YEAR([Planned Finish]@row) - IF(MONTH([Planned Finish]@row) = 1, 0, -1), 2)

This is a much simpler formula than what I had, so thank you again for helping me get this up and running!

Paul Newcome

Happy to help. 👍️

I saw the missing square bracket and could have sworn I put it back in there. Ugh. As for the end, I thought the FY for Feb 2023 would be FY23. I didn't realize it would be FY24.

Sorry about that but glad you were able to get it squared away.

Colleen Jones

Yeah, we're going by financial year, so for us February - April 2023 is FY24. Confusing, which is why I decided to create a formula to autofill the field for the team. :) Thanks again for your help!