Formula to assign fiscal quarter not working

Kelly Ospina
Kelly Ospina ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!