Solved MS Excel formula IF statement needed?

April 7, 2017 at 22:31:31
Specs: Windows 10
First I am trying to figure out a way to see if there is a Date entered into (A2) and then check to see if there is a Date in (B2) and then a (C2) and a (D2) and based on the answer, place the latest date into E2 and then calculate the number of days/years in between those dates.

For instance in this example… (A2) has a date but (B2), (C2), & (D2) do not. So the lastest activity is 2/26/2001 and that needs to be placed into (E2)

A2 = 2/26/2001
B2 = Null
C2 = Null
D2 = Null
E2 = 2/26/2001

In this example… (A3) has a date and (B3) has a date but (C3), & (D3) do not. So the lastest activity is 2/23/2005 and that date needs to be placed into (E3)

A3 = 11/12/1999
B3 = 02/23/2005
C3 = Null
D3 = Null
E3 =


In this example… (A4) has a date, (B4) has a date, (C4) has a date but (D4) does not. So the lastest activity is 5/3/2011 and that date needs to be placed into (E4)

A4 = 11/12/1999
B4 = 02/23/2005
C4 = 5/3/2011
D4 = Null
E4 = 5/3/2011


See More: MS Excel formula IF statement needed?

Report •

✔ Best Answer
April 9, 2017 at 07:21:01
Put this in E4. It will return a blank cell if there is no dates in the range:

=IF(COUNT(A4:D4)>0,MAX(A4:D4),"")

The double-quotes return a blank cell. Anything you put between the double quotes, e.g. "No Data", will be treated as a text string. Since there is nothing between "", the formula will return "nothing". Technically, it has to return something, so it returns a blank cell.

Put this in F4 and drag it to H4:

=IF(COUNT(A4:B4)=2,DATEDIF(A4,B4,"y") &" years, "&
DATEDIF(A4,B4,"ym") &" months, " &
DATEDIF(A4,B4,"md") &" days","")

The columns will increment as you drag it to the right, so it will always be checking to see if both cells contain data via the COUNT function.

Just an FYI...

re: "the following formula "=MAX(A2:D2)" does not work when all these cells are empty. It says "1/0/1900""

Technically, it works just fine. Here is the logic:

- If all cells are blank, the MAX function returns a 0.
- Dates are stored in Excel as integers and Times are stored as the decimal portion of the Date number.

e.g. 4/9/2017 10:05AM is stored internally as 42834.42035

- Day 0 in Excel is 1/0/1900, Day 1 in Excel is 1/1/1900, etc.

e.g. 4/9/2017 10:05AM is 42834.42035 days since 1/0/1900 00:00AM (the beginning of time as far as Excel is concerned.)

- If a cell contains a 0, e.g. =MAX(blank cells) and then formatted as a Date, Excel will see that as Day 0 and display 1/0/1900.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
April 8, 2017 at 05:08:04
This should return the latest date in a range:

=MAX(A2:D2)

re: "then calculate the number of days/years in between those dates."

Do you mean the days/years between A2 and the MAX date? Using the words "between those dates" is a bit confusing when you have 3 or 4 dates in the range.

In any case, this will calculate the difference between the MIN date and the MAX date:

=DATEDIF(MIN(A2:D2),MAX(A2:D2),"y") &" years, "&
DATEDIF(MIN(A2:D2),MAX(A2:D2),"ym") &" months, "&
DATEDIF(MIN(A2:D2),MAX(A2:D2),"md") &" days"

The generic formula is:

=DATEDIF(start,end,"y") &" years, "&
DATEDIF(start,end,"ym") &" months, " &
DATEDIF(start,end,"md") &" days"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 8, 2017 at 05:54:15
Thanks for your input. I'm getting a little closer to what I am needing.
I have placed your code "=MAX(A2:D2)" into (E2) and it does report the latest date.
My question would be how to calculate the days/year base on each date. For instance.

A4 = 11/12/1999
B4 = 02/23/2005
C4 = 5/3/2011
D4 = Null
E4 =MAX(A2:D2)

Cell (E4) does return the last date in the row using "=MAX(A2:D2)" and that is what I am needing. But now in the above example. I need the Days/year for between (A4) and (B4) and then I need
the Days/year for between (B4) and (C4).

If there was a date in (D4) then I would need the Days/year for between (C4) and (D4) etc.. The dates that are between A2:D2 vary and some cells between A2:D2 are blank. (empty)

I have a column setup to calculate the days between (A2) and (A3) etccc.and then another column to calculate the years between (A2) and (A3) and also columns setup to calculate the days between (A3) and (A4) etc.. I hope I cleared this up. It's a little confusing. Is there a way to post my Excel file?


Report •

#3
April 8, 2017 at 10:33:04
1 - What do you mean by "Days/year"? What kind of answer are you looking for in this example?

A4 = 11/12/1999
B4 = 02/23/2005

I gave you the generic formula to calculate the years/months/days between cells that contains dates. The answer for the above example is

5 years, 3 months, 11 days

Is that not what you want?

2 - re: "some cells between A2:D2 are blank."

In all of your examples, the empty cells are at the "end". i.e. you never show anything like the following. Will the following never occur?

A4 = 11/12/1999
B4 = Null
C4 = 02/23/2005
D4 = 05/3/2011

3 - In all of your examples, the dates are in ascending order. Is that always the case?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 8, 2017 at 11:40:10
Yes, the example you provided did solve part of my problem. Thank you!!
The answer to your question #2 - No, your example will never occur. All the dates are in ascending order and it will always be the case. In some other rare cases there is nothing in any of these cells (A4), (B4), (C4), (D4) so I would need for the formula to NOT show "0 years, 0 months, 0 days" but instead say something like "No Data"

Keeping with that same concept the following formula "=MAX(A2:D2)" does not work when all these cells are empty. It says "1/0/1900"

I hope I answered your questions and I really appreciate you time with this.


Report •

#5
April 8, 2017 at 14:51:26

Report •

#6
April 9, 2017 at 07:21:01
✔ Best Answer
Put this in E4. It will return a blank cell if there is no dates in the range:

=IF(COUNT(A4:D4)>0,MAX(A4:D4),"")

The double-quotes return a blank cell. Anything you put between the double quotes, e.g. "No Data", will be treated as a text string. Since there is nothing between "", the formula will return "nothing". Technically, it has to return something, so it returns a blank cell.

Put this in F4 and drag it to H4:

=IF(COUNT(A4:B4)=2,DATEDIF(A4,B4,"y") &" years, "&
DATEDIF(A4,B4,"ym") &" months, " &
DATEDIF(A4,B4,"md") &" days","")

The columns will increment as you drag it to the right, so it will always be checking to see if both cells contain data via the COUNT function.

Just an FYI...

re: "the following formula "=MAX(A2:D2)" does not work when all these cells are empty. It says "1/0/1900""

Technically, it works just fine. Here is the logic:

- If all cells are blank, the MAX function returns a 0.
- Dates are stored in Excel as integers and Times are stored as the decimal portion of the Date number.

e.g. 4/9/2017 10:05AM is stored internally as 42834.42035

- Day 0 in Excel is 1/0/1900, Day 1 in Excel is 1/1/1900, etc.

e.g. 4/9/2017 10:05AM is 42834.42035 days since 1/0/1900 00:00AM (the beginning of time as far as Excel is concerned.)

- If a cell contains a 0, e.g. =MAX(blank cells) and then formatted as a Date, Excel will see that as Day 0 and display 1/0/1900.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
April 9, 2017 at 07:59:18
Excellent. All works great.
Question. I have another column that tracks Last Activity and basically checks cell (E2) and returns the dates since that date. It works but if there is nothing in (E2) I get error.

=DATEDIF(E2,TODAY(),"y") &" Years, "&
DATEDIF(E2,TODAY(),"ym") &" Months, "&
DATEDIF(E2,TODAY(),"md") &" Days"


Report •

#8
April 9, 2017 at 12:10:45
What "error" do you get? If E2 is empty, I get a result:

117 Years, 3 Months, 9 Days

If you are getting an actual error, then something else is going on. Without knowing what error you are getting, there's not much I can do.

That said, just use an IF to check and see if E2 is not equal to nothing:

=IF(E2<>"", the formula, the result you want if E2 is empty)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
April 9, 2017 at 13:29:10

Thanks for helping. This is what I get. Since (E2) is getting result from the following code...

=IF(COUNT(A4:B4)>0,MAX(A4:B4),"")

If the code above return nothing cause there is not anything in those cells i.e. (A4) & (B4), then (E2) will be empty.

This code below is in cell (F2) to read cell (E2) and that's where I get the following error... "#VALUE!"

=DATEDIF(E2,TODAY(),"y") &" Years, "&
DATEDIF(E2,TODAY(),"ym") &" Months, "&
DATEDIF(E2,TODAY(),"md") &" Days"

I hope this helps... Thanks again..


Report •

#10
April 9, 2017 at 14:38:24
Thank you for explaining the error and that makes sense, however, I already gave you the solution to your latest issue:

=IF(E2<>"", the formula, the result you want if E2 is empty)

Don't take this wrong way. I only ask because I have no clue what your level of Excel expertise is:

Do you know how an IF function works? i.e. Do you understand the concept of this syntax?

IF( logical_test , [value_if_true], [value_if_false] )

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
April 9, 2017 at 15:44:02

Hello,

Yes, I do understand how IF works and no worries, I am not offended.

I put together the final code using the IF statement..

=IF(E2<>"",DATEDIF(E2,TODAY(),"y") &" years, "&
DATEDIF(E2,TODAY(),"ym") &" months, "&
DATEDIF(E2,TODAY(),"md") &" days", "No data to report")

Thanks again for your assistance. Now that I have the code working I can figure out how to graph the results.

Thanks again.


Report •

Ask Question