Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time in formulas (2003)

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

    Thanks again for saving me

    Aunt Linda

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •