Years, Months, Days, Hours, Minutes, Seconds Formula?

  • Thread starter Thread starter Darrell Burnett
  • Start date Start date
D

Darrell Burnett

Hi Everyone,

Could anyone PLEASE help me with a single cell formula that would take the
difference between TWO cells containing Dates AND Times, and return the
elapsed difference in the following format:

"3 Years, 9 Months, 15 Days, 14 Hours, 8 Minutes, 16 Seconds"

I've been trying all day to come up with it, but so far have only managed to
do the date part!

I would be extremely grateful for any help anyone can offer.

Thank you.
 
Hi Darrell,
As long as you have date & time in each cell as entered by Excel
you simply subtract the earlier timestamp from the later timestamp
to get a differences in days and/or hours and/or minutes
but since months do not have the same number of days
and years don't have the same number of days, you
probably want to use the DateDiff
see
http://www.cpearson.com/excel/datedif.htm


To get the formatting you want you can create a text value as follows
=DATEDIF(B3,C3,"y")&" years, "&DATEDIF(B3,C3,"ym")&" months, "&DATEDIF(B3,C3,"md")&" days "&TEXT(MOD(C3-B3,1),"hh "" hours "" mm
"" minutes "" ss ""seconds""")
 
Hi Dave,

Thank you SO much for that. I just used it, and it works absolutely
perfectly!

Incidentally, I HAD been using Chip Pearson's site, but just couldn't find
out how to put the date and the time formatted correctly in the SAME
formula. I had also been using John Walkenbach's ((excel)lent) 'Excel 2000
Formulas' book, but just couldn't find how to do it there either.

YOUR site is now in my favourites!

Thank you again.

Best wishes Darrell Burnett.


----- Original Message -----
From: "David McRitchie" <[email protected]>
Newsgroups: microsoft.public.excel
Sent: Friday, January 12, 2007 3:26 AM
Subject: Re: Years, Months, Days, Hours, Minutes, Seconds Formula?
 
Hello David,

I suggest this won't give the correct result in all cases, i.e. if the time
portion of B3 is later in the day that the time portion of C3 then the result
will be out by a day, for example

B3 = 13 Jan 2007 14:00
C3 = 14 Jan 2007 12:00

result should be

"0 years, 0 months, 0 days 22 hours 00
minutes 00 seconds"

but result returned is

"0 years, 0 months, 1 days 22 hours 00
minutes 11 seconds"

I suggest

=DATEDIF(B2,C2-(MOD(B2,1)>NOW()-TODAY()),"y")&" years,
"&DATEDIF(B2,C2-(MOD(B2,1)>NOW()-TODAY()),"ym")&" months,
"&DATEDIF(B2,C2-(MOD(B2,1)>NOW()-TODAY()),"md")&" days
"&TEXT(MOD(C2-B2,1),"hh "" hours "" mm
"" minutes "" ss ""seconds""")
 
Sorry, "result returned" for example given should be

"0 years, 0 months, 1 days 22 hours 00 minutes 00 seconds"

=DATEDIF(B2,C2-(MOD(B2,1)>NOW()-TODAY()),"y")&" years,
"&DATEDIF(B2,C2-(MOD(B2,1)>NOW()-TODAY()),"ym")&" months,
"&DATEDIF(B2,C2-(MOD(B2,1)>NOW()-TODAY()),"md")&" days
"&TEXT(MOD(C2-B2,1),"hh "" hours "" mm
"" minutes "" ss ""seconds""")
 
I don't question the seconds, which was asked for, I just assumed
Darrell had some actual data with start and end times through seconds.
If not use INT(timestamp) and use TODAY() for current date
instead of NOW() for a complete timestamp.

I think there will be whole days differences in how people
perceive the answers to be. After all there is no absolutely
correct answer. I did include more information on my webpage
afterwards. http://www.mvps.org/dmcritchie/excel/datetime.htm#datedif

and the page I previously mentioned is on Chip Pearson's website
http://www.cpearson.com/excel/datedif.htm
DATEDIF Worksheet Function is really more for determining a
person's age using dates, the important part is recognizing a birthdate
and people born on Feb 29 are going to celebrate a birthday on those
non leapyears and claim they are only 20 when they are about 80
(unless money is involved).



"daddylonglegs" wrote
 
Hello David,

I'm sorry, my second post might have been confusing, I was just correcting
my own typo from my original post.

I realise that the results of DATEDIF can be subjective to a certain extent,
with variable month lengths and leap years making a definitive answer hard to
agree upon, however in my example, where there is only 22 hours between the
time stamps, your suggested formula will give 1 day and 22 hours which, I
would contend, would be wrong by anybody's definition.

This error will occur whenever the time part of B3 is later in the day than
the time part of C3.

My proposed solution will adjust by 1 to account for this
 
Hi "daddylonglegs",
Thanks for correcting me a second time, as I missed the point.

You are correct, your example should have used the time portion
in the comparison from both start and end. I have corrected my
example on my webpage for when the time portion of the ending
date timestamp is less than the time portion the starting date timestamp.
http://www.mvps.org/dmcritchie/excel/datetime.htm#datedif

Your technique of subtracting TODAY() from NOW() stemming from
me trying to create some test data, would not be actually be used in
entering start and end time data.

B2: is the start timestamp (Date & time)
C2: is the end timestamp (Date & time)

D2: =DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"y")&" years, "
&DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"ym")&" months, "
&DATEDIF(B2,C2 -(MOD(B2,1)>MOD(C2,1)),"md")&" days, "
&TEXT(MOD(C2-B2,1),"hh "" hours, "" mm "" minutes, and "" ss ""seconds""")

example:
B2: 2007-01-12 23:00:01
C2: 2007-01-13 12:20:08
D2: 0 years, 0 months, 0 days, 13 hours, 20 minutes, and 07 seconds
 
hi thanks for helping i advanced i realy needed this because i do research and store them on dvds adn i want to track how old my dvd's are so
i have two main column 1 date burned and other disc age the formula is


=DATEDIF(C8,NOW(),"y") & " years, " & DATEDIF(C8,NOW(),"ym") & " months, " & DATEDIF(C8,NOW(),"md") & " days"&TEXT(MOD(C8-B8,1), " hh "" hours "" mm
"" minutes "" ss ""seconds""")


so if i type in one of cell in date burned 3.5am3.4.2000 the result in disc age should be
10 years, 11 months, 27 days 03 hours 05 minutes 00 seconds
so the issue is even that i changed the format in date brned to h.mAM/PMd.m.yyyy it still when i type like that the result will be come #VALUE! so what should i do
 
Back
Top