IF statement

MCiort - Aug 30, 2019 at 07:22 AM
 Blocked Profile - Aug 30, 2019 at 09:03 AM
I am really struggling to get the correct formula for below situation:

Cell A2 is a "due date"
Cell B2 is a "completion date"
Cell C2 is a "current progress"
In cell C2 I am trying without success to insert an IF formula so that:
1 - if A2 <= Today() then value in C2="Open - Late"
2 - if A2 > Today() then value in C2="Open - On time"
the 2 above conditions, considering B2 is empty
when B2 is filled in, what i am looking to include in the formula is that
3 - if A2 >= B2 then value in C2="Completed - On time"
4 - if A2 < B2 then value in C2="Completed - Late"

the formula I tried so far looks like below:

=IF(A2<=Today(),"Open-Late",IF(A2>Today(),"Open-On time",IF(A2>=B2,"Completed-On time",IF(A2<B2,"Completed-Late"))))

However, using the above, only first 2 conditions are returned in C2, therefore not sure how best to include all conditions so that the value in C2 is returned as per above conditions

Thanks a lot for your help!

1 reply

Ok, just swap the order in which you check for status. So check for a completed late first, completed on time next, and so on.

Also, on first check, use this:
If (and (b2 <>"", a2 <b2),"COMP LATE",

So on first logic test, use AND ().



Hi Mark,

Thank you for your quick response.
Just tried changing the order, however, still only first 2 conditions are returned.
Maybe, I did not explained clearly what I needed the formula to do, but basically I need all 4 returned values available.

if B2 is not filled, than the value in C2 is either Open Late / On Time and when B2 is filled, then the value returned to be based on condition between A2 and B2 --> Completed On time / Late.

for some reason, the current formula only works for the first 2 conditions.

Not sure what am I missing in the formula, or even if this is the right one to use
Hi Mark,

My apologies as only saw first 2 lines of your reply and completely omitted the second part of your reply stating to use AND().

In meantime sorted the issue with below:
=IF(B2="",(IF(A2<Today(),"Open-Late",IF(A2>=Today(),"Open-On time"))),IF(A2>=B2,"Completed-On Time",IF(A2<B2,"Completed-Late","")))

Also just to confirm that using the formula suggested by you, also solved the issue.

Thank you a lot for your quick response and help.
Blocked Profile
Aug 30, 2019 at 09:03 AM
Thank you for the feedback. Nested if statements can be tricky!