date comparing formula returns wrong result for one specific date value

Question

This might be easy to see for some of you but I struggle to find a solution. I have following formula:

IF(
AND(TODAY()<=E34;ISBLANK(I34));"Not started";IF(
AND(TODAY()>E34;ISBLANK(I34));"Start delayed";IF(
AND(TODAY()>I34;TODAY()<=F34;ISBLANK(J34));"In progress";IF(
AND(TODAY()>F34;ISBLANK(J34));"Completion delayed";IF(
AND(TODAY()>J34);"Done";"ERROR")
))))

Which compares the dates in cell E34-F34 and I34-J34 depending on the values it returns"not started","start delayed","in progress","completion delayed","done" or "Error.

E34 has date "01.06.2018"-F34 has "31.07.2018" and I34 has "01.06.2018" while J34 has "31.07.2018". Formula returns "Error" although it should return "done".

If F34 and J34 have as values 30.07.2018 instead of 31.07.2018 then formula returns "done". (It should have returned same "done" value for 31.07.2018 too)

I attach a picture of my situation. I hope someone can help

Answer 1

I am a little unclear on what the real desired conditions are. In case of there being actual text rather than dates I used:

=IF(TODAY()>1*SUBSTITUTE(J34;".";"/");"Done";IF(
AND(TODAY()<=1*SUBSTITUTE(E34;".";"/");ISBLANK(I34));"Not started";IF(
AND(TODAY()>1*SUBSTITUTE(E34;".";"/");ISBLANK(I34));"Start delayed";IF(
AND(TODAY()>1*SUBSTITUTE(I34;".";"/");TODAY()<=1*SUBSTITUTE(F34;".";"/");ISBLANK(J34));"In progress";IF(
AND(TODAY()>1*SUBSTITUTE(F34;".";"/");ISBLANK(J34));"Completion delayed";"ERROR")
))))

AND(TODAY()>J34);"Done";"ERROR") doesn't need an AND and only evaluates to Done if date is greater than J34. I would move it to the front and have it as first condition tested. 31/7/2018 is not > TODAY(). Today is 31/07/2018 therefore is equal.

If your dates are actual dates you don't need the SUBSTITUTE parts. You can check if actual dates using the ISNUMBER function e.g. ISNUMBER(E34) will return True if the cell contains a date not a text string.

Without Substitute

=IF(TODAY()>J34;"Done";IF(
AND(TODAY()<=E34;ISBLANK(I34));"Not started";IF(
AND(TODAY()>E34;ISBLANK(I34));"Start delayed";IF(
AND(TODAY()>I34;TODAY()<=F34;ISBLANK(J34));"In progress";IF(
AND(TODAY()>F34;ISBLANK(J34));"Completion delayed";"ERROR")
))))

If there is still a problem I would suggest stating each of the expected test conditions and giving some input dates and expected output.

The example you gave of Error was correctly evaluated.