# Thread: Time in formulas (2003)

1. ## Time in formulas (2003)

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

2. ## Re: Time in formulas (2003)

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.

3. ## Re: Time in formulas (2003)

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!) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

4. ## Re: Time in formulas (2003)

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

Thanks again for saving me

Aunt Linda

5. ## Re: Time in formulas (2003)

But you get the same Thank You!

Aunt Linda

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•