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.




  • p_bureau

    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 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!