+ Reply to Thread
Results 1 to 8 of 8

How to calculate time worked: Start time, end time, lunch break

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    How to calculate time worked: Start time, end time, lunch break

    I have a sheet which has the start, stop and time taken for lunch breaks.

    I want to calculate the total time worked such that i have the hours worked. So if i worked 7 hours and 30min then the total time would be 7.5hrs. But i am not sure how to take into account the lunch break time (see enclosed sheet).

    anyone have any ideas?
    Attached Files Attached Files
    Last edited by welchs101; 05-31-2011 at 08:11 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: How to calculate time worked: Start time, end time, lunch break

    One way:

    =G2-F2/60/24


    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: How to calculate time worked: Start time, end time, lunch break

    exactly what i wanted. thanks.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: How to calculate time worked: Start time, end time, lunch break

    You're welcome. Thanks for the rep.

    Regards

  5. #5
    Registered User
    Join Date
    11-18-2015
    Location
    Vancouver, Canada
    MS-Off Ver
    pro plus 2010
    Posts
    1

    Re: How to calculate time worked: Start time, end time, lunch break

    hi

    i just tried the formula and it does not work on the spreadsheet from which the problem was posed.

    am i doing something wrong in placing that formula in the total including lunch column?

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: How to calculate time worked: Start time, end time, lunch break

    My interpretation is that the OP has calculated the total elapsed time between the start and end times. He presumably is not going to want a figure that adds the lunch break on top of that. Hence, the calculation subtracts the lunch break (minutes) from the total elapsed time.

    The formula goes in cell H2 and is copied down. The value in cell G2 is the End Time - the Start Time. The formula, =G2-F2/60/24, converts the value (in minutes) in cell F2 and converts it into a time value, then subtracts it from the value in G2. Terminology feels wrong (IMO), but the answer posed seemed to be what the OP was looking for. At least, that's how I interpreted "exactly what i wanted. thanks." And, as far as I am aware, he has not come back to complain in the last 4+ years.

    In what way do you mean "it does not work on the spreadsheet from which the problem was posed." What doesn't it do that you expect it to? Or vice versa?

    As far as I am concerned, it does.

  7. #7
    Registered User
    Join Date
    04-24-2020
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    2

    Re: How to calculate time worked: Start time, end time, lunch break

    Hi, my calculation is slightly different, I want to calculate the time spent on a task within a shift with a set break time, but when the tasks start after the break time the break is deducted resulting in a negative time. How do I rectify this?

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: How to calculate time worked: Start time, end time, lunch break

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

+ 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