+ Reply to Thread
Results 1 to 7 of 7

Calculate total hours automatically deducting lunch breaks

  1. #1
    Registered User
    Join Date
    07-27-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    2

    Calculate total hours automatically deducting lunch breaks

    Hi all,

    I have a dillemma here,

    I have to create a schedule which incorporates lunches automatically (either half hour or 1-hour lunches, depending on the circumstances).

    The schedule only has time in and time out, but I need excel to automatically deduct the lunch break depending on the circumstance:

    If you worked less than 6 hours = no lunch
    If you worked more than 6 hours, but less than or equal to 8 hours = 0.5 hour lunch
    If you worked more than 8 hours = 1 hour lunch

    I am assuming the formula for this will be really long, but I have looked online everywhere and have not found ONE formula for it. I can't put lunch breaks seperately, so all I have to work with is Time in/out.

    Also, I wouldn't be able to use military time, so I don't know how excel can assume that time in is in the morning, and time out is in teh evening and/or half-day.

    If someone can help it would be greatlyyyy appreciated

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculate total hours automatically deducting lunch breaks

    It's easier if you can input times in a format excel can understand so for 9 to 5 either

    9:00 AM in A2 and 5:00 PM in B2 or 09:00 and 17:00

    Then you can use this formula for total hours

    =(B2-A2)*24-IF((B2-A2)*24>8,1,IF((B2-A2)*24>6,0.5,0))

    format result cell as number

  3. #3
    Registered User
    Join Date
    07-27-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Calculate total hours automatically deducting lunch breaks

    THANK YOU DADDYLONGLEGS!!!

    This is EXCELLENT!

    is there any way excel can automatically recognize the first time in as AM, and the second as PM?

    9:00
    5:00

    ?

  4. #4
    Registered User
    Join Date
    07-07-2009
    Location
    Orange County, California
    MS-Off Ver
    Microsoft 365; Excel 2010
    Posts
    40

    Re: Calculate total hours automatically deducting lunch breaks

    YES, but you have to use military time, ie, format cell to time (am/pm) and use 9 for am and 17 for pm (5pm)

  5. #5
    Registered User
    Join Date
    08-30-2016
    Location
    Texas
    MS-Off Ver
    10
    Posts
    7

    Re: Calculate total hours automatically deducting lunch breaks

    Hello,
    I have a question similar to the one above. I have the total time, but I need to add 30 minutes to it if they worked over 8hours. this is what I have below.
    in my cell B3 I have 12:21 so in my B4 cell my formula is =B3+TIME(0,30,0) Which gives me 12:51 that is correct, but if they didn't work 8 hours, I don't want it to add the 30 min.
    I thought that it could be =if(b3>8,b3+time(0,30,0)) but that isn't working. any suggestions?

    Thanks,
    Aseneth

  6. #6
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Re: Calculate total hours automatically deducting lunch breaks

    Asking another question in some others thread is not according to the forum rules

    Start a new thread.
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  7. #7
    Registered User
    Join Date
    03-03-2019
    Location
    uk
    MS-Off Ver
    business
    Posts
    1

    Re: Calculate total hours automatically deducting lunch breaks

    Thank you for this, its been a great help.

    Can you help a little further with my predicament? I'm looking to create a rota, this has helped calculate hours minus lunch breaks however I also need to add a an additional routine on how to remove/discount/not include additional text like sick/maternity/holiday etc. so that the total hours calculated do not include text for miscellaneous information and count it as zero.

    please can you help with this?

    kind regards billy

+ 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