IF statement [Solved]

- - Latest reply: ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
- Aug 30, 2019 at 09:03 AM
Hello,
I am really struggling to get the correct formula for below situation:

example:
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!
See more 

1 reply

Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1568
0
Thank you
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 ().

AND (FIRST TEST, SECOND TEST)

IF (AND (FIRST, SECOND), TRUE, FALSE)

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.

i.e.
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.
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1568 -
Thank you for the feedback. Nested if statements can be tricky!
Respond to ac3mark