Solved

IF statement (I think)

Posted on 2014-01-29
6
196 Views
Last Modified: 2014-02-03
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.
0
Comment
Question by:rocketship84
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 31

Expert Comment

by:Frosty555
ID: 39818157
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39818186
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
 

Author Comment

by:rocketship84
ID: 39818327
Thanks a million!
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:rocketship84
ID: 39818504
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
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39820241
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39820269
See attached with a few examples.

Thanks
Rob
Date-Comparison.xlsx
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question