In that case, you're going to have to get a little more "creative" with your formula.
As I said, the result in subtracting two date/times is a NUMBER. In your example, A1 = 10/12/2010 9:00:00 AM and B1 = 20/12/2010 10:00:00 AM results in: 10.0416666666642, or 10 days and .0416666666642 of a day. To have the cell show "10 Days", you need to "extract" the integer portion of the number: =INT(ABS(B1-A1)) and append the word "Days": =INT(ABS(B1-A1)) & " Days" ----------------------------------------- Next, you need the fractional part of the time difference. To do that, you can take the difference, and subtract the integer portion: ABS(B1-A1) - INT(ABS(B1-A1)) Since this is the "fractional part of a day", to convert this to hours, multiply by 24 hours/day: (ABS(B1-A1) - INT(ABS(B1-A1))) * 24 Now, there COULD be some rounding errors. In MY case, the result came out to be: 0.999999999941792 hours... So, you can use the ROUND() function to round it off to hours, and fractional hours. ROUND((ABS(B1-A1) - INT(ABS(B1-A1)))*24,1) and then append the string "Hours" ----------------------------------------------------------------- Combining these two techniques, you get a function like: =INT(ABS(B1-A1)) & " Days, " & ROUND((ABS(B1-A1) - INT(ABS(B1-A1)))*24,1) & " Hours" which will display as: 10 Days, 1 Hours Now, if you're REALLY wanting to make sure that if the number of days is (1), then simply say "Day" instead of "Days", and "Hour" instead of "Hours", You're going to have to include "IF()" statements, and your function becomes: =INT(ABS(B1-A1)) & IF(INT(ABS(B1-A1)) = 1," Day, "," Days, ") & ROUND((ABS(B1-A1) - INT(ABS(B1-A1)))*24,1) & IF(ROUND((ABS(B1-A1) - INT(ABS(B1-A1)))*24,1) =1," Hour", " Hours") Paul > >From: C.G.Kumar <kumar.bemlmum...@gmail.com> >To: excel-macros@googlegroups.com >Cc: schreiner_p...@att.net >Sent: Mon, December 20, 2010 4:03:07 AM >Subject: Re: $$Excel-Macros$$ Facing calculation issue using Time type in excel > >Could you please tell what if there is more than 1 day difference. Say A1 has >10/12/2010 09:00 and B1 is 20/12/2010 10:00, then put Formulae in C1 as >ABS(B1-A1) in time format it gives result as 10/01/1900 1:00:00 . Actual it >should be 10 Days and 1 Hours. > > > > > >Any suggestion will be appreciated. > > > > >Thanks & Regards, > > >C.G.Kumar > > > > > > > >On Sat, Dec 18, 2010 at 10:29 PM, Bharghav Ramdas <bhargha...@gmail.com> wrote: > > >>Thanx Paul. >> >> >> >>On Thu, Dec 16, 2010 at 1:31 AM, Paul Schreiner <schreiner_p...@att.net> wrote: >> >>Keep in mind that Excel doesn't know "time". >>>It knows "numbers". >>>Time is simply the "fractional part of a day" >>> >>>9:30 is 0.395833333333333 of a day. >>>10:00 is 0.416666666666667 of a day. >>> >>>so, 9:30 - 10:00 is: -0.020833333333334 >>>Now really, is 12/15/2010 -01:30 PM a valid time? >>>Of course not... how can you have a NEGATIVE time of day? >>> >>>So, since you're DISPLAYING the cells in a TIME format, >>>it produces an error with the negative result. >>> >>>If you don't CARE about the sign, then you can use: >>>=ABS(A1-A2) >>>and format it as "time" to get: 0:30 >>> >>>If you DO care about the sign, then you need to change the display format to >>>a >>>numeric format. >>> >>>Paul >>> >>> >>>> >>>>From: Bharghav Ramdas <bhargha...@gmail.com> >>>>To: excel-macros@googlegroups.com >>>>Sent: Wed, December 15, 2010 1:39:36 PM >>>>Subject: $$Excel-Macros$$ Facing calculation issue using Time type in excel >>>> >>>> >>>> >>>>Hi All, >>>> >>>>When I subtract 9:30 from 10:00 (Data Type :Time),all I get to see is >>>>###.How do >>>>I go about resolving this issue.I require a positive number 0:30 inspite of >>>>the >>>>result turning out to be negative. >>>> >>>>FYI >>>>=(1-2) >>>> Current Expecting >>>>1 9:30 9:30 >>>>2 10:00 10:00 >>>> #### 0:30 >>>> >>>>Let me know ur suggestions. >>>> >>>>Thanks, >>>>Bharghav R-- >>>>---------------------------------------------------------------------------------- >>>> >>>>Some important links for excel users: >>>>1. Follow us on TWITTER for tips tricks and links : >>>>http://twitter.com/exceldailytip >>>>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>>>3. Excel tutorials at http://www.excel-macros.blogspot.com/ >>>>4. Learn VBA Macros at http://www.quickvba.blogspot.com/ >>>>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/ >>>> >>>>To post to this group, send email to excel-macros@googlegroups.com >>>> >>>><><><><><><><><><><><><><><><><><><><><><><> >>>>Like our page on facebook , Just follow below link >>>>http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts >>>> >>>-- >>>---------------------------------------------------------------------------------- >>> >>>Some important links for excel users: >>>1. Follow us on TWITTER for tips tricks and links : >>>http://twitter.com/exceldailytip >>>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>>3. Excel tutorials at http://www.excel-macros.blogspot.com >>>4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>>To post to this group, send email to excel-macros@googlegroups.com >>> >>><><><><><><><><><><><><><><><><><><><><><><> >>>Like our page on facebook , Just follow below link >>>http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts >>> >>-- >>---------------------------------------------------------------------------------- >> >>Some important links for excel users: >>1. Follow us on TWITTER for tips tricks and links : >>http://twitter.com/exceldailytip >>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>3. Excel tutorials at http://www.excel-macros.blogspot.com >>4. Learn VBA Macros at http://www.quickvba.blogspot.com >>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >>To post to this group, send email to excel-macros@googlegroups.com >> >><><><><><><><><><><><><><><><><><><><><><><> >>Like our page on facebook , Just follow below link >>http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts >> >-- >---------------------------------------------------------------------------------- > >Some important links for excel users: >1. Follow us on TWITTER for tips tricks and links : >http://twitter.com/exceldailytip >2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >3. Excel tutorials at http://www.excel-macros.blogspot.com >4. Learn VBA Macros at http://www.quickvba.blogspot.com >5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > >To post to this group, send email to excel-macros@googlegroups.com > ><><><><><><><><><><><><><><><><><><><><><><> >Like our page on facebook , Just follow below link >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
