# years-months-days-hours

R

#### Raz

can anyone tell me how to do this, might be very simple. thanks in advance

i have two dates/time in this format mm/dd/yyyy hh:mm:ss
17/10/2001 12:35:20
21/12/2009 09:40:00

on another cell I would like the difference between these two dates in this
format.

8 years, 11 months, 24 days, 00 hours, 20 min, 23 seconds.

or each in a different cell like these:
8 years
11 months
24 days
00 hours
20 min
23 seconds

Hi,

Try these which assume dates/times in a1 & A2

=DATEDIF(A1,A2,"y")&" Years"
=DATEDIF(A1,A2,"ym")&" Months"
=DATEDIF(A1,A2,"md")&" Days"
=TEXT(A2-A1,"ss")& " Hours"
=TEXT(A2-A1,"m")& " Minutes"
=TEXT(A2-A1,"s")&" Seconds"

They can all be concatenated into a single cell if you want
=DATEDIF(A1,A2,"y")&" Years " & DATEDIF(A1,A2,"ym")&" Months " etc
Mike

I can see the 8 years but I can't see 11 months nor can I see 24 days?

I get 8 years, 2 months and 4 days?

With the earlier date in A1 and the later in A2

=DATEDIF(A1,A2,"Y")

to get the years

=DATEDIF(A1,A2,"YM")

to get the months after the years have been removed

=DATEDIF(A1,A2,"MD")

to get the days after the years and months have been removed

To get the hours

=HOUR(MOD(A2-A1,1))

I let you figure out the rest yourself

--

Regards,

Peo Sjoblom

Hmm,

I didn't test the time parts well enough, ignore those

Mike

Thanks, most of it is working except this.

years, months, days, hours and sec are working.
but for some reason minutes not working correctly. (always giving me 1 min)

here is what I tried:

12/9/2009 8:00:20
12/9/2009 9:00:20

and its calculating this

01 Hours
01 Minutes
00 Seconds

Thanks David,
this is what Mike gave as well, but my minutes not calculating right, no
matter what date/hours i put, its calculating 1 or 01 minutes. any idea why?