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/2001In 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

✔ 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.

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.

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?

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/2005I 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/20113 - 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.

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.

Done for today. More tomorrow.

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

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.

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"

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.

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..

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.

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.

Ask Your Question

Weekly Poll