Excel has it's own internal date/time functionality. A "date" is the number of days since 1/1/1900. "time" is a fractional part of a day.
So.. 3/1/2011 @ 6:25am is actually: 40238.2673611111 and 3/2/2011 @ 20:25 is actually 40239.8506944444 To find the time difference, simply subtract the two values. Now, since your "time" is not represented in Excel time format, we need to convert it. If the "time" (6.25) actually represents time-of-day (6:25) then... you can use the TimeValue function: First, replace the "." with ":" so that the string "looks" like a time value: SUBSTITUTE(D2,".",":") Then, put that into the TimeValue function: TIMEVALUE(SUBSTITUTE(D2,".",":")) This will provide the hours. Next, add the Date to the hours C2+TIMEVALUE(SUBSTITUTE(D2,".",":")) Do the same for the "Check Out" time: F2+TIMEVALUE(SUBSTITUTE(G2,".",":")) Subtract the two date/time(s) =((F2+TIMEVALUE(SUBSTITUTE(G2,".",":"))) - (C2+TIMEVALUE(SUBSTITUTE(D2,".",":")))) This will give you the number of days (and partial days) between the two dates. Simply multiply by the number of hours/day (24) gives you: =((F2+TIMEVALUE(SUBSTITUTE(G2,".",":"))) - (C2+TIMEVALUE(SUBSTITUTE(D2,".",":"))))*24 ------------------------------------------------------ Now, if the time value does NOT represent hh:mm, then we'll have to do something else to convert it to a fraction of a day. (like: D2/24 ??) hope this helps, Paul ________________________________ From: Rajesh K R <rajeshkainikk...@gmail.com> To: excel-macros <excel-macros@googlegroups.com> Sent: Mon, March 7, 2011 11:22:53 AM Subject: $$Excel-Macros$$ Calculate Time Hi Experts , Kindly check the attached file and tell how to calculate the hours of stay in column "I". Regards Rajesh kainikkara -- ---------------------------------------------------------------------------------- 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/discussexcel -- ---------------------------------------------------------------------------------- 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/discussexcel
