I need to find the yy/mm/dd AND hh:mm:ss between 2 time stamps

[Pontoon]

I need to work out the number of years, months, days AND the elapsed time
between two timestamps. I can compare dates OR Times, but not both together.

If I do a simple subtract ( A1-A2 ) with the results in A3 formatted using
the custom format
yy" Years" mm" Months" dd" Days" hh" Hours" mm" Minutes" ss" seconds"

it almost works - HOWEVER Excel seems to add a spurious month to the results.

(It seems to also do this even if I enter a zero into a cell, then format it
using the same custom format! )

I must be doing something stupid, so any help would be more than welcome.

 

[David Biddulph]

I need to work out the number of years, months, days AND the elapsed time
between two timestamps. I can compare dates OR Times, but not both
together.

If I do a simple subtract ( A1-A2 ) with the results in A3 formatted using
the custom format
yy" Years" mm" Months" dd" Days" hh" Hours" mm" Minutes" ss" seconds"

it almost works - HOWEVER Excel seems to add a spurious month to the
results.

(It seems to also do this even if I enter a zero into a cell, then format
it
using the same custom format! )

I must be doing something stupid, so any help would be more than welcome.

Don't forget that when you format a number as a date/time in Excel, the yy
mm dd are a date (counting from 1st January 1900).
Hence if you feed it a number 6, for example, it will be treated as 6th
January 1900 if you try to format it as a date, and if you split that format
up the dd part will be 6 and the mm part will be 01 (for January).

You may do better with the DATEDIF function:
http://www.cpearson.com/excel/datedif.htm

 

[Pontoon]

Thanks David,
But ( as I understand it ) DATEDIF will only show me one of the attributes
that I am after - number of days or years or hours between tow dates... I am
trying to get all that together.

So for example what I want is to be able to enter 01/01/2006 09:00:00 in
one cell and 18/03/2006 10:10:01 in another and for Excel to tell me that
there is 0 Years, 2 months, 16 days, 1 hour, 10 miuntes and 1 second between
them.

Currently ( with excel subtracting one cell from the other, and the results
cell custom formatted as
yy" Years" mm" Months" dd" Days" hh" Hours" mm" Minutes" ss" seconds"
Then I get the answer I expect ( but with one extra month ) !!

If I compare 2 identical time stamps the result is all zeros exept for one
month.... and the weird thing is that if I format the results cell as a
number it shows ZERO ( so I know that the calculation is correct .) It must
be a problem with the custom format???

 

[David Biddulph]

Thanks David,
But ( as I understand it ) DATEDIF will only show me one of the attributes
that I am after - number of days or years or hours between tow dates... I
am
trying to get all that together.

So for example what I want is to be able to enter 01/01/2006 09:00:00 in
one cell and 18/03/2006 10:10:01 in another and for Excel to tell me that
there is 0 Years, 2 months, 16 days, 1 hour, 10 miuntes and 1 second
between
them.

Currently ( with excel subtracting one cell from the other, and the
results
cell custom formatted as
yy" Years" mm" Months" dd" Days" hh" Hours" mm" Minutes" ss" seconds"
Then I get the answer I expect ( but with one extra month ) !!

If I compare 2 identical time stamps the result is all zeros exept for one
month.... and the weird thing is that if I format the results cell as a
number it shows ZERO ( so I know that the calculation is correct .) It
must
be a problem with the custom format???

My previous message told you why you've got a 1 month difference doing it
your way, & pointed you at Chip Pearson's DATEDIF site.

If you follow Chip's DATEDIF recommendations, & go on from where you were
with the time part, it should work.

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

gives
0 years 2 months 17 days 01 hours 01 minutes 01 seconds

which is, I think, the right answer. [I think your answer is a day
adrift.?]

 

[David Biddulph]

Thanks David,
But ( as I understand it ) DATEDIF will only show me one of the
attributes
that I am after - number of days or years or hours between tow dates... I
am
trying to get all that together.

So for example what I want is to be able to enter 01/01/2006 09:00:00 in
one cell and 18/03/2006 10:10:01 in another and for Excel to tell me
that
there is 0 Years, 2 months, 16 days, 1 hour, 10 miuntes and 1 second
between
them.

Currently ( with excel subtracting one cell from the other, and the
results
cell custom formatted as
yy" Years" mm" Months" dd" Days" hh" Hours" mm" Minutes" ss" seconds"
Then I get the answer I expect ( but with one extra month ) !!

If I compare 2 identical time stamps the result is all zeros exept for
one
month.... and the weird thing is that if I format the results cell as a
number it shows ZERO ( so I know that the calculation is correct .) It
must
be a problem with the custom format???

My previous message told you why you've got a 1 month difference doing it
your way, & pointed you at Chip Pearson's DATEDIF site.

If you follow Chip's DATEDIF recommendations, & go on from where you were
with the time part, it should work.

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

gives
0 years 2 months 17 days 01 hours 01 minutes 01 seconds

which is, I think, the right answer. [I think your answer is a day
adrift.?]

Sorry, too much of a hurry, some of the above was wrong, of course.

=MOD(A2-A1,1) formatted as hh" Hours" mm" Minutes" ss" seconds" gets the hh
mm ss part right

For the days part I think it needs (or otherwise it goes wrong when the
earlier date has a later time of day):
=DATEDIF(B1,B2,"y")&" years "&DATEDIF(B1,B2,"ym")&" months
"&DATEDIF(B1,B2-IF(MOD(B1,1)>MOD(B2,1),1,0),"md")&" days "

I haven't yet been able to get the two parts to combine, as I couldn't get
TEXT() to work with the format I wanted for the time part (even when I
corrected the places above where I'd erroneously put hh when I meant mm &
ss).

 

[Pontoon]

Thanks David,
It doesn't matter that the two parts of this are in differnet cells... I can
just put the different portions of this is adjacent cells and align them
appropriatley.

Many thanks for your help with this. I doubt whether I woudl have managed to
figure this out myself.

Cheers


Pontoon