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"

"Rick Rothstein (MVP - VB)" wrote:

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


"daddylonglegs" wrote in message
...
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"



"kathi" wrote:

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