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

Find more posts tagged with

Sales

Calendar View

Formulas

IT & Operations

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…