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

  • Paul Newcome
    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"))))

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • J. Craig Williams
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!