Time in formulas (2003)

Topic

New Reply

Time sheet – employee will enter arrival and departure times ONLY – no provision to enter 1 hour lunch break. What formula to I write to compute the total number of hours worked that will subtract that hour for lunch? I’m stumped (oops! almost wrote the truth – “stupid” apparently)

Aunt Linda

Reply | Quote

Replies

Say arrival time is in A1 and departure time in B1. The hours worked can be computed using the formula

=B1-A1-1/24

or

=B1-A1-TIME(1,0,0)

You’ll have to format the result with a time format. If you want the result to be a number of hours with a decimal, e.g. 6.5 instead of 6:30, use

=24*(B1-A1)-1

and format the result as a number with the desired number of decimal places.

Reply | Quote

Duh – apparently I forgot there are 24 hours in a day . . .

Thanks again for saving me

Aunt Linda

Reply | Quote

If Time In is in A1, and Time Out is in B1, try:
=B1 – A1 – 1/24
formatted as time

The reason is that excel stores times as “fractions of a day” so 1/24 represents one hour

drat! Hans beat me by 0:02 (or 0.00139 of a day!)

Reply | Quote

But you get the same Thank You!

Aunt Linda

Reply | Quote