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
