Dates Using IF function?

[Jwinter]

Hi

First time posting so please be gentle

I have a set two sets of dates one in column E (Target Date) and one in O (Actual Finish Date)

The sheet I have inherited uses the below formula:

=IF(O2="",IF(E2>NOW(),"OPEN","OVERDUE"),IF(O2>E2,"FAIL","PASS"))

What I need the new formula to do is:

If the target date has not yet passed return - Open
If the target date has been passed by the actual finish date but, is within 5 days or less of the target date return - Pass Late
If the target date has been passed by the actual finish date or is blank, date return - Overdue
If the target date was met by the actual finish date either on the date of the target date or before this return, Pass

I've been tinkering on and off for about a week now and for the life of me i just can't crack it. Any help would be appreciated.

 

[Joe4]

Welcome to the Board!

You have not nested your IF function properly. IF functions only have 3 arguments.
Take a look here for details and examples, and see if that helps you figure it out: MS Excel: How to use the Nested IF Functions (WS)

If you run into issues, please post back.

 

[Jwinter]

Welcome to the Board!

You have not nested your IF function properly. IF functions only have 3 arguments.
Take a look here for details and examples, and see if that helps you figure it out: MS Excel: How to use the Nested IF Functions (WS)

If you run into issues, please post back.

Thank you for the response

At the risk of coming across as thick, I'd tried the below, but they seem to just return whatever I input as the first value if true;

=IF(E2>NOW(),"OPEN",IF(E2-O2<5,"PASS - LATE",IF(O2<E2,"PASS",IF(O2>E2,"FAIL"))))

I've tried more than the one formula but the above is the latest.

I'm not entirely sure that I'm telling excel correctly about the 5 day requirement. I've taken a look at the link, and will re look now to see if anything jumps out at me, but if you do have any other pointers.examples please feel free to share

 

[Joe4]

Note that IF statements read from left to right, and as soon as it finds a "TRUE" condition, it will stop there. So the order that you list them in is important.
So, if the date in cell E2 is after today, it is going to return "OPEN", and not look at anything else in the rest of the formula.
As such, you usually want to check the "more restrictive" conditions first, and the less ones, last.

If this does not help, please provide an example of that is in cells E2 and O2, so we can try to recreate your test that is not working.
Also, can you confirm that E2 and and O2 are valid date entries, and not actually text entries?
One easy way to check is to enter these formulas somewhere on your sheet, and see what they return:
=ISNUMBER(E2)
=ISNUMBER(O2)

Since dates are really stored in Excel as numbers, they should both return TRUE.
If they do not, you are dealing with Text entries that must be converted to Date values first.