+ Reply to Thread
Results 1 to 3 of 3

Time Calculation For A Timesheet To Include Lunch

  1. #1
    poddys
    Guest

    Time Calculation For A Timesheet To Include Lunch

    I have seen many postings for time calculation but nothing that
    includes a factor for lunch.
    I am trying to create a Timesheet so I can calculate the number of
    hours worked per day, but have not been able to find a way to include
    the Lunch time in the formula and to have it displayed correctly.

    I have cells for Start Time and End Time formatted as HH:MM, plus Lunch
    formatted as Numeric. Example: Start 8:30AM, End 5:30PM, Lunch 1:00

    A formula of End - Start (ie: D4-C4) will give me 9:00 (9 hours).
    How can I deduct the 1 hour for lunch?
    This is not a time, just a number of hours/minutes.
    I tried converting to all different values with no luck.
    Thanks in advance.
    Tony


  2. #2
    Johnny Meredith
    Guest

    Re: Time Calculation For A Timesheet To Include Lunch

    Assume cell A1 has time in, B1 has time out, and C1 has the number of
    hours (not minutes) for lunch. The formula would be:

    =HOUR(B1)-HOUR(A1)-C1

    If you want to track lunch in hours and minutes, I think there's a
    minute function out there (did not test).

    HTH,
    Johnny


  3. #3
    poddys
    Guest

    Re: Time Calculation For A Timesheet To Include Lunch

    Thanks very much for your help - it looks as if many helpful people are
    already responding to my request - Thanks to you all....

    I did FINALLY manage to solve the problem as follows:

    =D4-C4-TIME(0,E4*60,0)

    C4 is my Start Time, D4 is my End Time, and E4 is the Lunch Time. I
    tried to deduct this as hours which was ok for whole hours, so I
    changed my TIME formula to multiply the Lunch by 60 (ie: 1.00 is 60
    minutes, 0.50 is 30 minutes) and that worked.

    Such a simple task has to be so complicated....
    Thanks again everyone.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1