Hour Tracking with Multiple Overtime Tiers and Shift Premiums

[Hortiman]

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

 

[Jeffrey Mahoney]

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

 

[Hortiman]

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]

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

	B	C	D	E	F	G	H	I	J	K	L	M
1		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total	Factor	Rate	Amount
2	Proj 1	1
3	Proj 2			4
4	Proj 3	3		4
5	Proj 4
6	Proj 5	4	4	1	5
7	Proj 6		5
8	Proj 7	2	3
9	Proj 8
10	Proj 9			3	3	5	6
11	Proj 10
12	Proj 11
13	Proj 12		1	2		10	10
14	Proj 13
15	Proj 14
16	Proj 15
17	Total Hours:	10	13	14	8	15	16	0	76
18	Daily ST Hours:	10	12	12	8	12	12	0	66
19	Cum. Daily ST Hours:	10	22	34	42	54	66	66	66
20	ST hours Remaining:	10	12	12	6	0	0	0	40	1.0	10.0	400
21	Daily OT Hours:	0	1	2	0	3	4	0	10	1.5	15.0	150
22	Weekly OT Hours:	0	0	0	2	12	6	0	20	1.5	15.0	300
23	Weekly DT Hours:	0	0	0	0	0	6		6	2.0	20.0	120
24	Check:	10	13	14	8	15	16	0

<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 Cell Formula 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]

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