time calculation question

Posted by Lynn on August 30, 2001 7:27 AM

is it possible to create a formula that will calculate time - as in a time sheet - based on time in and time out?

Posted by Barrie Davidson on August 30, 2001 7:33 AM

I assume you are formating your time as a normal number (not as time) so that 1 hour and 15 minutes show up as 1.25 hours. Assuming start time in A1 and end time in B1, use

=(B1-A1)*24

Barrie

Posted by Jay Deitch on August 30, 2001 2:54 PM

If you are using Excel "Time" values, you can just subtract them.

First format columns A, B and C as "Time" as follows...

Select columns A & B then go to "Format -> Cells," select "Time" from the Number tab and pick a format type you like. Select column C and format it as time with a format like 37:30:55 (which is hours:minutes:seconds).

After that, enter a time in A1 like "10:00 am" and another in B1 like "5:00 pm" (include the space between the number and the "am" or "pm" otherwise Excel will think it is just text). If you put =A1-B1 in C1 it will display "7:00:00"

Jay