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

=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"
--
David Biddulph

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

"Raz" wrote:

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

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


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

Hmm,

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

Mike

"Mike H" wrote:

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

"Raz" wrote:

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

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"
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=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"
--
David Biddulph

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

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








"Mike H" wrote:

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

"Raz" wrote:

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

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" wrote:

=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"
--
David Biddulph

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




.

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. :-)
--
David Biddulph

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

=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"
--
David Biddulph

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




.