Hour Tracking with Multiple Overtime Tiers and Shift Premiums

Hortiman

New Member
Joined
Feb 13, 2019
Messages
3
Hi,

I'm putting together a timesheet for my company and I'm having a bit of trouble doing hour totaling. On the same sheet I have rows for different job numbers and columns for days of the week. I also have 2 sections on one sheet, one relating to regular time worked and one relating to hours worked with a shift premium. On top of that I also have different overtime tiers, of 1.0X, 1.5X, and 2.0X if the employee works 40, 40-60, and 60+ hours respectively. Also If an employee works more than 12 hours in a day the hours above 12 are also 1.5X.

On top of all that I'm trying to set it up to take the 1st 40 hours worked as straight time whether they are regular hours or hours worked at a premium, the next 20 at 1.5X, and anything above at 2.0X, while still tracking whether they are regular or premium hours, with over 12 hours worked in a day going directly to 1.5X Overtime on regular or premium hours.

I currently set up to count the hours of each type (regular or premium) by day and week, and split them into the overtime tiers based on total hours worked but I'm at a loss on how to add the other rules.

I know this is difficult to describe with words so i'm sorry for that, but hopefully it still makes sense. I'm fairly new to excel so any help would be greatly appreciated.

Thanks
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
Welcome to the site. Or at least congrats on your first post.

I don't want to be negative so please take this as constructive criticism.

* You didn't ask a question
* People can't help you unless you tell us exactly what part you are stuck on
* Examples of the tables really help
* We aren't going to do it for you, but we REALLY want to help you!
* If it's a lot of steps you need help with, start at the first step and go from there.

Good luck.

In general, add all the employee hours for the period and create formulas for each tier. Lets say column E contains the total hours, B is ST rate, C is OT, D is DT. Column F could contain the amount for the first 40
=max(E2,40)*B2
Column G is the amount for Overtime:
=if(E2>40,max(E2-40,20)*C2,0)
Column H is the amount of Double time
=if(E2>60,E2-60*D2,0)

Something like that.

Jeff
 
Last edited:

Hortiman

New Member
Joined
Feb 13, 2019
Messages
3
Hey Jeff,

Yeah, sorry for the improper posting etiquette, I'll try to address some of those issues.

The way I have it setup is with the days of the week across the top with totals for each day along the bottom, and job numbers along the left with total hours per job along the right and a total hours for the week in the bottom right corner. This layout is done once for regular and again for premium hours below it.

At the bottom of everything is a row for total hours each day of the week from from. Both ref and premium and total hours for the week. Below all of this I have it setup to calculate the overtime for each type of hour separately similar to what you described.

Where I am stuck is how to move forward from here. I'll try to talk it through, but I'm not sure how to move from the theory to the excel functions or which ones to use to do it most efficiently.

I need to take the numbers from the total hours for the week row and sum them up day by day, Mon to Sun, until 40 is hit. Then, continuing through the week mon-sun, the next 20 hours need to be logged in as 1.5x, under the regular or premium hours depending on what was worked, and anything above this needs to be logged as 2.0x under regular or premium.

So essentially once the sum of mon+tues+wed... reaches 40 it needs to start referencing whether the hours are regular or premium, sum them in the correct cell and then sum between the 1.5x reg and 1.5x premium cells until 20, then any hours above this need to reference reg or pre and summed in the correct 2.0x cell.

On top of all of this I also need to be able to take any daily total hours > 12 and automatically sum them in the correct 1.5 or 2.0 cell.

So I guess the best place to start is how do I:

A) get excel to sum cells sequentialy in a row from a-z in a given row to max 40?

B) get excel to reference cells once a preset value is reached.something like If ("function A", "reference cells in set range and sort", "")
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
Here are my questions as I read through your last post.

Do you have a table for each employee?
Are you accounting for the fact that premium hours per day are not counted toward premium hours per week? (Only Straight time can be used to calc OT and DT per week).

In the example below, I kinda over simplified the formulas so it was easier to see what is happening. The key row is the cumulative daily straight time. It is used to figure out how many ST hours are remaining after the daily OT has been removed. This is used by the weekly OT and Weekly DT calculations. The ST hours remaining is after the daily OT, Weekly OT, and Weekly DT has been removed.

You can combine formulas or simply hide the rows you don't need to see.


Excel 2013/2016
BCDEFGHIJKLM
1MonTueWedThuFriSatSunTotalFactorRateAmount
2Proj 11
3Proj 24
4Proj 334
5Proj 4
6Proj 54415
7Proj 65
8Proj 723
9Proj 8
10Proj 93356
11Proj 10
12Proj 11
13Proj 12121010
14Proj 13
15Proj 14
16Proj 15
17Total Hours:10131481516076
18Daily ST Hours:10121281212066
19Cum. Daily ST Hours:1022344254666666
20ST hours Remaining:1012126000401.010.0400
21Daily OT Hours:0120340101.515.0150
22Weekly OT Hours:00021260201.515.0300
23Weekly DT Hours:00000662.020.0120
24Check:101314815160

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
M20=J20*L20
L21=$L$20*K21
M21=J21*L21
L22=$L$20*K22
M22=J22*L22
L23=$L$20*K23
M23=J23*L23
C17=SUM(C2:C16)
D17=SUM(D2:D16)
E17=SUM(E2:E16)
F17=SUM(F2:F16)
G17=SUM(G2:G16)
H17=SUM(H2:H16)
I17=SUM(I2:I16)
J17=SUM(C17:I17)
C18=MIN(C17,12)
D18=MIN(D17,12)
E18=MIN(E17,12)
F18=MIN(F17,12)
G18=MIN(G17,12)
H18=MIN(H17,12)
I18=MIN(I17,12)
J18=SUM(C18:I18)
C19=SUM($C$18:C18)
D19=SUM($C$18:D18)
E19=SUM($C$18:E18)
F19=SUM($C$18:F18)
G19=SUM($C$18:G18)
H19=SUM($C$18:H18)
I19=SUM($C$18:I18)
J19=I19
C20=C17-SUM(C21:C23)
D20=D17-SUM(D21:D23)
E20=E17-SUM(E21:E23)
F20=F17-SUM(F21:F23)
G20=G17-SUM(G21:G23)
H20=H17-SUM(H21:H23)
I20=I17-SUM(I21:I23)
J20=SUM(C20:I20)
C21=MAX(C17-12,0)
D21=MAX(D17-12,0)
E21=MAX(E17-12,0)
F21=MAX(F17-12,0)
G21=MAX(G17-12,0)
H21=MAX(H17-12,0)
I21=MAX(I17-12,0)
J21=SUM(C21:I21)
C22=MAX(MIN(C19,60)-40,0)-SUM($B$22:B22)
D22=MAX(MIN(D19,60)-40,0)-SUM($B$22:C22)
E22=MAX(MIN(E19,60)-40,0)-SUM($B$22:D22)
F22=MAX(MIN(F19,60)-40,0)-SUM($B$22:E22)
G22=MAX(MIN(G19,60)-40,0)-SUM($B$22:F22)
H22=MAX(MIN(H19,60)-40,0)-SUM($B$22:G22)
I22=MAX(MIN(I19,60)-40,0)-SUM($B$22:H22)
J22=SUM(C22:I22)
J23=SUM(C23:I23)
C23=MAX(C19-60,0)-SUM($B$23:B23)
D23=MAX(D19-60,0)-SUM($B$23:C23)
E23=MAX(E19-60,0)-SUM($B$23:D23)
F23=MAX(F19-60,0)-SUM($B$23:E23)
G23=MAX(G19-60,0)-SUM($B$23:F23)
H23=MAX(H19-60,0)-SUM($B$23:G23)
C24=SUM(C20:C23)
D24=SUM(D20:D23)
E24=SUM(E20:E23)
F24=SUM(F20:F23)
G24=SUM(G20:G23)
H24=SUM(H20:H23)
I24=SUM(I20:I23)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Hortiman

New Member
Joined
Feb 13, 2019
Messages
3
Wow, Yes thats a huge step in the right direction. I'll try to mess with that in my workbook later today but it seems like it is exactly what I've been trying to produce.

In response to your questions:

- Yes all employees will have their own sheet in the workbook. I also have a master sheet that populates job numbers, names and dates across all employee sheets in the workbook to ensure they are all uniform as well as a summary sheet that sums up all of the hours from all employees for tracking time spent each week across all projects.

- Premium time will count towards OT each week. For example if an employee is on night shift mon-wed at 10 hours a day then standard time thurs-sun they would get:30 hours premium straight time, 10 hours regular straight time, then 20 hours regular 1.5X and 10 hours 2.0X time. If they worked mon-wed regular time then thurs-sun premium time they would get: 30 hours regular straight time, 10 hours premium straight time, 20 hours premium 1.5X and 10 hours premium 2.0X time
 

Watch MrExcel Video

Forum statistics

Threads
1,109,030
Messages
5,526,344
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top