Paul, I must admit that you are genius as the way of you explained the thing in such a lucid manner that a naive person like me understood at first sight... Unbelievable ..Many Many Thanks again to you Paul as you devoted your precious time for us...
Thanks & Regards, C.G.Kumar On Mon, Dec 20, 2010 at 7:36 PM, Paul Schreiner <schreiner_p...@att.net>wrote: > 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 > -- ---------------------------------------------------------------------------------- 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
