Your substitute command is REMOVING the "." completely.
You should be REPLACING the "." with a colon (":")Next... is there always a "."? If not, then we're going to have to look at alternatives. Paul ________________________________ From: Rajesh K R <rajeshkainikk...@gmail.com> To: excel-macros@googlegroups.com Sent: Tue, March 8, 2011 10:16:48 AM Subject: Re: $$Excel-Macros$$ Calculate Time Hi Paul You give me excellent answer thank you very much, but when I give the formula timevalue(substitute(d2,".","")) it shows error value #value!. pls check it. Regards Rajesh kainikkar On 3/7/11, Paul Schreiner <schreiner_p...@att.net> wrote: > 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 > -- ---------------------------------------------------------------------------------- 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
