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.
rocketship84Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Frosty555Commented:
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
0
 
Rob HensonFinance AnalystCommented:
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
0
 
rocketship84Author Commented:
Thanks a million!
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rocketship84Author Commented:
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")
0
 
Rob HensonFinance AnalystCommented:
Not sure why you have had to add the ">0.1" at each point.

You are comparing dates and ascertaining whether the difference between dates is more than 1/10 of a day!!! Are you including time as well? 1/10 of a day is 2.4 hours or 2 hours 24 minutes.

Breaking the formula out into bits:

If no date in F23 use section "TODAY()-E23>0.1" this will result in TRUE or FALSE and not a value, then determine whether this is greater than 0.1 and return YES or No. Whether you get TRUE or FALSE, they are both greater than 0.1 so will give "Yes".

So if no date in F23 you will get "Yes".

If there is a date in F23, compare it to within 2.4 hours of E23.

Are you sure you are getting the result you want???

Thanks
Rob H
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Rob HensonFinance AnalystCommented:
See attached with a few examples.

Thanks
Rob
Date-Comparison.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.