 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2007Years, Months, Days, Hours, Minutes, Seconds Formula?
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""") --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > Hi Everyone, > [quoted text clipped - 10 lines] > > Thank you. 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" <dmcritchie_xlmvp@verizon.net> Newsgroups: microsoft.public.excel Sent: Friday, January 12, 2007 3:26 AM Subject: Re: Years, Months, Days, Hours, Minutes, Seconds Formula? > Hi Darrell, > As long as you have date & time in each cell as entered by Excel [quoted text clipped - 33 lines] >> >> Thank you. 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""") > Hi Darrell, > As long as you have date & time in each cell as entered by Excel [quoted text clipped - 30 lines] > > > > Thank you. 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""") > Hello David, > [quoted text clipped - 57 lines] > > > > > > Thank you. 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). --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > 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 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 > 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. [quoted text clipped - 26 lines] > > B3 = 13 Jan 2007 14:00 > > C3 = 14 Jan 2007 12:00 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 --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > 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 [quoted text clipped - 11 lines] > > > B3 = 13 Jan 2007 14:00 > > > C3 = 14 Jan 2007 12:00
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.