+ Reply to Thread
Results 1 to 6 of 6

Calculating time with lunch breaks

  1. #1
    Registered User
    Join Date
    08-17-2016
    Location
    Florida, USA
    MS-Off Ver
    2010; 2013
    Posts
    9

    Post Calculating time with lunch breaks

    Hello everyone,
    I have a problem that I've been trying to figure out the best way to solve it and have not been successful. I'm hoping that one of you amazing people would be able to assist.
    I have a time sheet that includes employee’s names, in times, out times, and break times. I’m trying to create a formula that will give me the length of their shifts on any given day. Here’s the catch: It needs to be able to give me the length minus a lunch break (of 30 min) if the length is equal to, or greater than 6 hours, but if less than that, it should give me the length without the lunch break. Having said that, each shift changes every day, and if I send any one of them home early or they come in late, I will change their out time or in time in the sheet, so the length column needs to update live.

    I've attached a screen shot of the file so you can see what I'm looking at.

    Any ideas are more than welcome.
    Thank you!
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Calculating time with lunch breaks

    Welcome ICB,

    Assuming the cell under length is H3, enter this formula and copy down.

    =IF(G3-C3+(C3>=G3)>(6/24),G3-C3+(C3>=G3)-(0.5/24),G3-C3+(C3>=G3))

    This works out the time difference, if it is more than 6 hours, it deducts half an hour. I have ignored the Break Time column as it is irrelevant. The (C3>=G3) is a trick to deal with times that run past midnight where a 1 needs to be added to a negative answer.
    I hope this helps, please let me know!

    Regards,

    David


    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


  3. #3
    Registered User
    Join Date
    08-17-2016
    Location
    Florida, USA
    MS-Off Ver
    2010; 2013
    Posts
    9

    Re: Calculating time with lunch breaks

    David,
    Thank you so much. That worked perfectly, but it created a second problem that I wasn't expecting. The amount of staff changes from day to day, so my spreadsheet has empty cells that will have this formula imputed, but since they are empty, it returns with an error. Is there a way to add another IF statement saying that if it comes back with an error give me number 0?

    Thank you again!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Calculating time with lunch breaks

    I assume that you mean you are getting an erroneous answer instead of an error message. When I put the formula in H3, leaving G3 and C3 blank I get a result of 23:30, which represents one day, because C3 is equal to G3, minus .5/24 (or half an hour). To solve this I would recommend:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    08-17-2016
    Location
    Florida, USA
    MS-Off Ver
    2010; 2013
    Posts
    9

    Re: Calculating time with lunch breaks

    Thank you so much both of you. This was exactly what I was looking for and it worked great!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Calculating time with lunch breaks

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help! Calculating Time between 8 ComboBoxes to obtain Total minus Lunch
    By ocnmel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2015, 04:28 PM
  2. [SOLVED] Need formula to find the difference between time and minus lunch and breaks
    By Shellybelly in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-09-2014, 05:13 PM
  3. [SOLVED] Calculating total minutes worked in real time, minus breaks and lunch.
    By uacdub in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-06-2014, 08:54 AM
  4. Replies: 10
    Last Post: 03-31-2014, 01:18 PM
  5. Calculating END time based on start time, breaks, and hours to complete
    By sweedey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-01-2012, 11:29 AM
  6. Excel 2007 : Calculating all lunch breaks in Timesheet
    By italiansun in forum Excel General
    Replies: 0
    Last Post: 11-02-2011, 05:37 PM
  7. Calculating time, potential lunch break
    By B1123 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-13-2008, 09:18 AM

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