IF formula with Condition on dates

MARIE CHRISTINE ANNIC GUYOMARC'H

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

 

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.

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.