years-months-days-hours

[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

 

[David Biddulph]

=DATEDIF(A1,A2,"y")&" years, "&DATEDIF(A1,A2,"ym")&" months,
"&DATEDIF(A1,A2,"md")&" days, "&TEXT(A2-A1,"hh")&" hours,
"&TEXT(A2-A1,"mm")&" minutes, "&TEXT(A2-A1,"ss")&" seconds"

 

[Mike H]

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

 

[Peo Sjoblom]

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

 

[Mike H]

Hmm,

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

Mike

 

[David Biddulph]

I realise that the minutes are wrong there.

Try
=DATEDIF(A1,A2,"y")&" years, "&DATEDIF(A1,A2,"ym")&" months,
"&DATEDIF(A1,A2,"md")&" days, "&TEXT(HOUR(A2-A1),"00")&" hours,
"&TEXT(MINUTE(A2-A1),"00")&" minutes, "&TEXT(SECOND(A2-A1),"00")&" seconds"

 

[Raz]

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

 

[Raz]

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?

 

[David Biddulph]

See my second reply (at 22:17 last night), with a revised solution. The
problem with my first formula was that it was confusing months and minutes,
and I think that Mike fell into the same trap (& again spotted it and
commented to that effect in another message at 22:17). Unfortunately Excel
uses mm as the abbreviation for both months and minutes.