Dates Using IF function?

Jwinter

New Member
Joined
Nov 11, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 11, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,264
Messages
5,600,599
Members
414,393
Latest member
Vignesh Mechz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top