Totaling DATEDIF SUMS

[kathi]

I have two (A&B) columns with dates in each
I them have (C) with the formula =DATEDIF(A6,B6,"y") & " years, " &
DATEDIF(A6,B6,"ym") & " months, " & DATEDIF(A6,B6,"md") & " days"
I need to total column (C)
OR
I have also revised it so that (C) is =B1-A1 resulting in a number string
and then formatting the cell to read yy " years, " mm " months, " dd " days"
but I still can't get a total of all these years...............

 

[daddylonglegs]

Hello kathi,

using =B1-A1 and formatting as yy " years, " mm " months, " dd " days" won't
give you the correct result, e.g. if B1-A1 =40 days that will (incorrectly)
give a result of 2 months and 9 days.

I'd stick with DATEDIF, then for a sum, assuming you have dates in rows 1 to
10, try

=DATEDIF(0,SUM(B1:B10)-SUM(A1:A10),"y")&" years,
"&DATEDIF(0,SUM(B1:B10)-SUM(A1:A10),"ym")&" months,
"&DATEDIF(0,SUM(B1:B10)-SUM(A1:A10),"md") & " days"

 

[Rick Rothstein \(MVP - VB\)]

And the OP should be made aware that DATEDIF has a minor problem when the
start date is the end of January and the end date is the first of March. For
example, using the OP's formula...

=DATEDIF(A6,B6,"y") & " years, " & DATEDIF(A6,B6,"ym") &
" months, " & DATEDIF(A6,B6,"md") & " days"

she should try these combinations...

A6: 1/31/2007 or 1/30/2007
B6: 3/1/2007

or

A6: 1/31/2008
B6: 3/31/2008

Rick

 

[Pete_UK]

If you put the three DATEDIF functions in separate columns, one for
year, month and day (without the text labels), then you will find it
easier to total them, with appropriate carry forwards.

If you use =B1-A1 in C1 this will give you the number of days
difference, so you can total these easily and then split into years,
months and days. You might like to keep column C like this (you can
hide the column) and then format column D to give the display how you
have it now in C.

Hope this helps.

Pete

 

[daddylonglegs]

That's a very good point, Rick.

This formula will give you very similar results to the 3xDATEDIF formula,
but without any negative numbers

=YEAR(B1)-YEAR(A1)-(TEXT(B1,"mmdd")< TEXT(A1,"mmdd"))&" years
"&MOD(MONTH(B1)-MONTH(A1)-(DAY(B1)< DAY(A1)),12)&" months
"&B1-MIN(DATE(YEAR(B1),MONTH(B1)-(DAY(B1)< DAY(A1))+{1,0},DAY(A1)*{0,1}))&"
days"