IF formula with Condition on dates

Hi there, can anyone help me on this formula ? it looks the only issue could be on the date format but I don't know what I have to change.

=IF([Expected Order Date]4 < 3 / 31 / 18, "1Q 18"; IF([Expected Order Date]4 < 6 / 30 / 18, "2Q 18",IF([Expected Order Date]4 > 9 / 30 / 18,"3Q 18","4Q 18",))))

Thanks a lot in anticipation.

Marie

 

Comments

  • Hi !

     

    Seems like in your formula you have sometimes ";" or ",". Moreover I always use the DATE formula :

     

    =IF([Expected Order Date]4 < DATE(2018,3,31), "1Q 18", IF([Expected Order Date]4 < DATE(2018,6,30), "2Q 18",IF([Expected Order Date]4 > DATE(2018,9,30),"3Q 18","4Q 18")))

     

    Does this help?

     

    Best regards,

     

    Paul.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    ^^^ Correct on the dates. What you're saying here =IF([Expected Order Date]4 < 3 / 31 / 18, "1Q 18";...  is:

     

    If the date in the 4th row of the Expected Order Date column is less than 3 divided by 31 divided by 18, then populate 1Q 18 in the target cell. To get the formula to calculate using a date you need to use DATE(YYYY,MM,DD).

     

    Also correct is that you should use a "," in place of the ";" to maintain proper syntax. I do know that various countries use different dividers (for example, Germany uses ";" in place of "," when building formulas), but it should be uniform throughout either way.

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!