calculating years months days hours minutes and seconds

[FDDavis]

I want to take a date in cell A12 being the begin date in the format
m/d/yyyy h:mm:ss and a date in cell b12 the end date in the same format
then in cell c12 have it display the difference in the format x
Years x Months x Days x Hours x Minutes and x Seconds which is
how many years, months, days, hours, minutes and seconds there are
between begin date and end date. the following formula gets me down to
the days but i am stumped as to how to get down to the hours,minutes
and seconds cell c12 is formated as general
=DATEDIF(A12,B12,"y")&" Years, "&DATEDIF(A12,B12,"ym")&" Months,
"&DATEDIF(A12,B12,"md")&" Days"

a12 is 1/3/2003 16:22:00
b12 is 2/2/2004 16:22:27
c12 results is 1 Years, 0 Months, 30 Days

Thanks for any help

 

[JE McGimpsey]

Perhaps something like:


=DATEDIF(A12,B12,"y")&" Years, "&DATEDIF(A12,B12,"ym")&" Months,
"&DATEDIF(A12,B12,"md")-(MOD(A12,1)>MOD(B12,1)) &" Days " &
HOUR(MOD(B12-A12,1)) & " Hours " & MINUTE(MOD(B12-A12,1)) & " Minutes "
& SECOND(MOD(B12-A12,1)) & " Seconds"

Note: you'll want to be careful about your formula, though. If:

A12: 1/31/2003
B12: 3/1/2003

then

=DATEDIF(A12,B12,"y")&" Years, "&DATEDIF(A12,B12,"ym")&" Months, "
& DATEDIF(A12,B12,"md")&" Days"

will return

1 Years, 1 Months, -1 Days

since datedif uses the number of days in the month of the first term.
There aren't a lot of good ways to avoid things like that since "months"
is a squirrelly concept at best.

 

[Norman Harker]

Hi FD!

Try!
=DATEDIF(A1,B1,"y")&" y "&DATEDIF(A1,B1,"ym")&" m
"&DATEDIF(A1,B1,"md")-(MOD(B1,1)<MOD(A1,1))&" d
"&SUBSTITUTE(TEXT((B1-INT(B1)-A1+INT(A1))+(SIGN(B1-INT(B1)-A1+INT(A1))
<0),"h:m"),":"," h ")&" m
"&INT(MOD(MOD(MOD((B1-A1),1)*24,1)*60,1)*60)&" s"

But you get problems with comparison of dates such as 31-Jan and
1-Mar. In preference to the above I'd use weeks rather than months:

=DATEDIF(A1,B1,"y")&" y
"&INT((DATEDIF(A1,B1,"yd")-(MOD(B1,1)<MOD(A1,1)))/7) & " w
"&(DATEDIF(A1,B1,"yd")-(MOD(B1,1)<MOD(A1,1)))-INT((DATEDIF(A1,B1,"yd")
-(MOD(B1,1)<MOD(A1,1)))/7)*7 & " d "&INT(MOD((B1-A1),1)*24)&" h
"&INT(MOD(MOD((B1-A1),1)*24,1)*60)&" m
"&INT(MOD(MOD(MOD((B1-A1),1)*24,1)*60,1)*60) & " s"

In passing, steer clear of the "yd" argument in DATEDIF because it
does come up with errors.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[FDDavis]

Thanks Both of these seem to do what I need
have a great da

 

[Norman Harker]

Hi FD!

Thanks for thanks. It's always appreciated and shows Google Searchers
that things work as required.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.