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).
Bookmarks