calculating years months days hours minutes and seconds

  • Thread starter Thread starter FDDavis
  • Start date Start date
F

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