+ Reply to Thread
Results 1 to 2 of 2

Calculating OT Hours Based on Work Outside of Core Business Hours

  1. #1
    Registered User
    Join Date
    11-23-2011
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    1

    Calculating OT Hours Based on Work Outside of Core Business Hours

    I have a situation that's certainly common among HR types (of which I am not), but darned if I can't figure it out.

    I have an existing timesheet that calculates OT based on total number of hours worked beyond 8 hours. Calculations appear on the Summary Page of the attached file (note: all relevant cells are shaded in yellow):
    • Daily Total Hours: Summary of all cells that contain work hours for that day (appears in cell K2)
    • Daily Regular Hours: =IF(K2>8,8,K2)
    • Daily OT Hours: =IF(K2>12,4,IF(K2>8,K2-8,0))
    • Double-OT Hours: =IF(K2>12,4,IF(K2>8,K2-8,0))

    Nice and simple, right?

    Naturally, this couldn't last as the attached timesheet now must calculate OT1 (time and a half) and OT2 (double-time) based on the following criteria:
    • Any work performed outside of core hours (6:00 AM to 5:00 PM) will be calculated as OT1
    • Saturday work: first four hours of Saturday work is paid at OT1 rate, beyond that at OT2
    • Sunday work: all hours paid at OT2 rate

    I'm a moderate Excel user at best, so I get easily confused using what I call "nested" conditional formulas (i.e., multiple IF statements, etc.). I supect that I should add a conditional statement that refers to cells that host my core hours (which appear in cells M3 and N3 of the attached), but darned if I can't fully wrap my head around what I need to do to accomplish the task.

    Your help is appreciated in advance and may even be reciprocated, especially if you have any Word or grammatical questions (I'm a tech writer).
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075

    Re: Calculating OT Hours Based on Work Outside of Core Business Hours

    I don't have time to work on this right now to see whether this works, but try wrapping another IF statement around your formulas in K3, K4 & K5 to test whether the day in D4 is a Sunday. If true, then direct hours=0, OT1= 0 and OT2=direct hours *2.
    Trish in Oz
    -------------
    A problem well defined is a puzzle half solved


    If you attach a sample of your workbook it will be easier to find a solution. Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
    http://www.excelforum.com/faq.php?fa...b3_attachments

+ 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