# Dates Using IF function?

#### Jwinter

##### New Member
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.

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### 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

##### New Member
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.

Replies
0
Views
48
Replies
10
Views
302
Replies
3
Views
146
Replies
5
Views
514
Replies
3
Views
181

1,127,140
Messages
5,622,959
Members
415,942
Latest member
Data Midwife

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back