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"))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!