Formula to assign fiscal quarter not working
Good morning,
I have been using a formula to assign a fiscal quarter to a row based on a date in another cell. I realized yesterday that the formula isn't working properly.
This is the formula: =IF(ISDATE([Estimated Ship Date]1), IF(MONTH([Estimated Ship Date]1) <= 6, "Q1", IF(MONTH([Estimated Ship Date]1) <= 9, "Q2", IF(MONTH([Estimated Ship Date]1) <= 12, "Q3", "Q4"))))
Our fiscal year runs from June 1st to May 31st, so a date of August 30th should produce a result of Q1. Instead, it is producting Q2. A date of May 30th should produce a result of Q4, but is producing Q1.
Hoping someone here might know how to fix this. Thank you!
Here is a link to a sample sheet: https://app.smartsheet.com/b/publish?EQBCT=fe9c6453229b414c9209cf40bbd9973d
Comments
-
You just need to adjust what determines each quarter within your formula.
The way your formula currently reads... If the month is less than or equal to 9, then it should be Q2. Which means any date in August WOULD be considered Q2.
Because of how your Fiscal year overlaps on the calendar year end, I would suggest using some AND statements to specify month ranges for each quarter. Something like this...
=IF(ISDATE([Estimated Ship Date]1), IF(AND(MONTH([Estimated Ship Date]1) >= 6, MONTH([Estimated Ship Date]1) <= 8), "Q1", IF(AND(MONTH([Estimated Ship Date]1) >= 9, MONTH([Estimated Ship Date]1) <= 11), "Q2", IF(OR(MONTH([Estimated Ship Date]1) = 12, MONTH([Estimated Ship Date]1) = 1, MONTH([Estimated Ship Date]1) = 2), "Q3", "Q4"))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!