Formula to assign fiscal quarter not working

Kelly Ospina

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

 

Paul Newcome

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"))))

J. Craig Williams

Here's a shorter version:

=IFERROR("Q" + VALUE(IF(MONTH([Estimated Ship]@row) <= 6, 2, -2) + INT((MONTH([Estimated Ship Date]@row) + 2) / 3)), "")

Craig