+ Reply to Thread
Results 1 to 6 of 6

Help with calculating overtime hours

  1. #1
    Registered User
    Join Date
    09-18-2017
    Location
    London
    MS-Off Ver
    Office 2010
    Posts
    11

    Help with calculating overtime hours

    Hello

    I wanted some help in calculating employee overtime and time-off in lieu. I have 2 data worksheets (“Original Saturday Overtime Hours” & “Sunday Overtime Hours”) the first shows the number of Saturday overtime and the second show the Sunday hours. They can have more than one entry in each and not necessarily have done both Sat and Sun overtime. I wanted a macro or formula to populate columns D and E so that the original Sat table looks like the “Final Saturday Overtime Hours” table with the additional data entered in the first row for each employee (where applicable) - please see attachment

    The logic:

    If employee only worked Saturday hours and no Sunday hours then nothing needs to be done (see Ritesh Patel)

    If an employee has less than 24 hours Saturday overtime, then the hours from their Sunday overtime is used to make up the difference, those hours used in making up the difference to reach to 24 hours need go into column “Time off in-Lieu”. Any remaining Sunday hours will go in column “Sunday hours to be paid” (see John Smith: did 13 Sat hours therefore used 11 of his Sunday hours to reach 24. 11 goes in column “Time off in-Lieu” and the remainder 15 Sunday hours goes into column “Sunday hours to be paid”)

    If an employee has done more than 24 Saturday hours then all his Sunday hours goes into column “Sunday hours to be paid” (see Jane Brown)

    If employee only did Sunday hours then add their details at the bottom and the first 24 hours to show in column “Time off in-Lieu” and the remainder in column “Sunday hours to be paid” (see Dan Jones & Abby Peter)

    Thank you

    overtime table.png

  2. #2
    Registered User
    Join Date
    09-18-2017
    Location
    London
    MS-Off Ver
    Office 2010
    Posts
    11

    Re: Help with calculating overtime hours

    Please see attached table in ExcelAttachment 738795

  3. #3
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,958

    Re: Help with calculating overtime hours

    Your attachment is invalid. Please follow instruction at the top on how to attach sample workbook.

  4. #4
    Registered User
    Join Date
    09-18-2017
    Location
    London
    MS-Off Ver
    Office 2010
    Posts
    11

    Re: Help with calculating overtime hours

    Please see attached the Excel file
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-18-2017
    Location
    London
    MS-Off Ver
    Office 2010
    Posts
    11

    Re: Help with calculating overtime hours

    File now attached

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

    Re: Help with calculating overtime hours

    Perhaps the following will help.
    1. Paste the following into cell J4 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. Paste the following into cell K4 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3. Select cells J4:K13 > select formatting (Ctrl + 1) > select custom > type 0;;; into the window
    Let us 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.

+ 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. [SOLVED] Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis
    By HumdrumPanic in forum Excel General
    Replies: 5
    Last Post: 09-30-2020, 12:55 PM
  2. [SOLVED] Calculating Overtime Over 40 Hours
    By jefraz2003 in forum Excel General
    Replies: 5
    Last Post: 03-25-2020, 05:50 PM
  3. help for formula Calculating Hours [Overtime hours]
    By tryhyper in forum Excel General
    Replies: 5
    Last Post: 07-03-2017, 04:16 AM
  4. [SOLVED] Calculating Overtime without going over 12 hours
    By ljmparalegal in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2015, 03:23 PM
  5. Calculating overtime hours
    By Tashia in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-19-2014, 02:09 AM
  6. Timesheet formula for calculating ordinary hours and overtime hours.
    By zoen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-18-2011, 04:23 AM
  7. Calculating Overtime Hours
    By Jonathan78 in forum Excel General
    Replies: 0
    Last Post: 11-02-2009, 05:49 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