Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Formula to calculate fiscal quarter when quarter starts on February
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!
Answers
-
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)
-
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!
-
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.
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!