asked on

IF statement (I think)

I am trying to create a formula that will solve YES/NO if a row is past-due. I am defining past due as the actual completed date being past the estimated completion date. However, the actual completed date is not entered until completion where the row may already be past due. Hence, I want to use the current date less the estimated completion date until the actual completion date is entered. The Today() date is in cell E2 Est. Completion date is in cell E4 actual completed date is in cell F4. I need a formula that will calculate past due based on E2-E4 until a date is placed in F4 then I would like the past-due to be calculated as E2-E4.

Frosty555

I think you want something like this:

DAYS OVERDUE:

=IF(ISBLANK(F3), TODAY()-E3, F3-E3)

Where F3 is the actual completion date
Where E3 is the estimated completion date

This formula will check if the actual completion date is blank. If it is, then the days overdue is calculated as the difference between TODAY and the estimated completion date. If the actual completion date is not blank, the days overdue is calculated as the difference between the actual completion date and the estimated completion date.

In either case, if the resulting number is >0, then the project is overdue. If it is <=0 the project is not overdue.

So create another cell which compares that:

=IF(H3>0, "YES", "NO")

Where H3 is the result of the top formula

Rob Henson

If you don't need to see the number of days but just a Yes or No, the calc for number of days can be included in the one formula:

=IF(IF(ISBLANK(F3), TODAY()-E3, F3-E3)>0, "YES", "NO")

Thanks
Rob

rocketship84

ASKER

Thanks a million!

rocketship84

ASKER

I had to make a small adjustment but now it works. Please see below:

=IF(IF(ISBLANK(F23), TODAY()-E23>0.1, F23-E23)>0.1, "YES", "NO")

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

See attached with a few examples.

Thanks
Rob
Date-Comparison.xlsx