+ Reply to Thread
Results 1 to 4 of 4

Adding Hours Worked minus lunch break IF over 5 hours

  1. #1
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Adding Hours Worked minus lunch break IF over 5 hours

    I'm attempting to make a simple time sheet for a handful of employees. I'd like to enter the clock in time and clock out time for each day. The end cell should be the running total for the week. The tricky part for me is having the formula subtract an hour for each day that is over 5 hours.

    I've attached an example spreadsheet

    Thanks for taking a look!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Adding Hours Worked minus lunch break IF over 5 hours

    Im sure someone will give you a far more eligant suggestion, but in the mean time, try this ugly thing...
    =IF(C5-B5>5/24,C5-B5-1/24,C5-B5) + IF(E5-D5>5/24,E5-D5-1/24,E5-D5) + IF(G5-F5>5/24,G5-F5-1/24,G5-F5) + IF(I5-H5>5/24,I5-H5-1/24,I5-H5) + IF(K5-J5>5/24,K5-J5-1/24,K5-J5) + IF(M5-L5>5/24,M5-L5-1/24,M5-L5)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: Adding Hours Worked minus lunch break IF over 5 hours

    Quote Originally Posted by FDibbins View Post
    Im sure someone will give you a far more eligant suggestion, but in the mean time, try this ugly thing...
    =IF(C5-B5>5/24,C5-B5-1/24,C5-B5) + IF(E5-D5>5/24,E5-D5-1/24,E5-D5) + IF(G5-F5>5/24,G5-F5-1/24,G5-F5) + IF(I5-H5>5/24,I5-H5-1/24,I5-H5) + IF(K5-J5>5/24,K5-J5-1/24,K5-J5) + IF(M5-L5>5/24,M5-L5-1/24,M5-L5)
    I had to add *24 after each parentheses but that ol' ugly thing worked... lol

    Thanks for the help!!!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Adding Hours Worked minus lunch break IF over 5 hours

    Happy to help

+ 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. Replies: 2
    Last Post: 11-11-2015, 05:51 AM
  2. Total Working Hours Minus Break + Lunch Times
    By Rosco88 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2013, 05:01 AM
  3. Replies: 8
    Last Post: 06-11-2013, 05:34 PM
  4. Replies: 4
    Last Post: 02-12-2013, 07:01 AM
  5. Replies: 2
    Last Post: 02-11-2013, 02:26 PM

Tags for this Thread

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