Calculate Overtime with multiple criteria

[bhalbach]

I am trying to make a timesheet that calculates overtime with the following criteria...

1. Overtime is after 8hrs on any given day.
2. Overtime is after 40hrs/week...consideration to the after 8hr criteria to ensure not duplicating overtime.
3. The after 40hrs/week overtime criteria changes if there is a Stat holiday...if there is one stat holiday in the week overtime is calculatd after 32hrs/week,

if there is two stat holiday in the week overtime is calculatd after 24hrs/week etc. Basically for every stat holiday during the week the overtime threshhold changes by 8hrs.

Spreadsheet is set-up with days of the week across the columns, employee names down the rows.
Daily total hours for each employee are entered in the appropriate cells and the formulas need to do all the overtime calculations.
I was planning on using text "Stat" in the cells were a stat holiday occured.

I have managed to calculate all the criteria except the "Stat" holiday criteria without circular references but I am now stumped and have not found a solution on any posts.​

 

[Dave Patton]

Please ensure that your explanation of how overtime is calculated is complete.

N.B. The Mon - Sun are actual dates formatted to just show day of the week.

Excel 2010
	C	D	E	F	G	H	I	J	K
2		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total
3	Hours	5	5	7	13	13	8	10	61
4	Regular								32
5	OT @ 1.5								29
1aaa

Cell Formulas
Range	Formula
K3	=SUM(D3:J3)
K4	=MIN(40-SUMPRODUCT(--(rHolidays=D2:J2))*8,SUM(D3:J3)-SUMPRODUCT(--(D3:J3>8),D3:J3-8))
K5	=K3-K4

Named Ranges
Name	Refers To	Cells
rHolidays	='1aaa'!$M$2:$M$25

 

[Dave Patton]

The example below includes another alternative. "Stat" is shown above the days of the week.

Excel 2010
	D	E	F	G	H	I	J	K	L	M
1	Stat				Overtime
2	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total	Reg	Overtime
3	5	5	7	13	13	8	10	61	32	29
4	10	10	10	10				40	32	8
1aaa

Cell Formulas
Range	Formula
K3	=SUM(D3:J3)
K4	=SUM(D4:J4)
L3	=MIN(40-SUMPRODUCT(--(rHolidays=D2:J2))*8,SUM(D3:J3)-SUMPRODUCT(--(D3:J3>8),D3:J3-8))
L4	=MIN(40-COUNTIF(D1:J1,"Stat")*8,SUM(D4:J4)-SUMPRODUCT(--(D4:J4>8),D4:J4-8))
M3	=K3-L3
M4	=K4-L4

Named Ranges
Name	Refers To	Cells
rHolidays	='1aaa'!$O$2:$O$22

 

[bhalbach]

What is the rHolidays? In cells O2:O22

I am an amateur at this so I may have some questions...

 

[Dave Patton]

What is the rHolidays?
The dates of the holidays are in a range of cells.
I named this range rHolidays.

The first line of the example counts the number of days in the week that are included in the range named rHolidays.

The second line of the example does not use the holiday dates; it just counts the number of columns with the heading "stat".

 

[bhalbach]

I had a quick look and it seems to be working...I will test it further and let you know

Thanks for assist...greatly appreciated

 

[bhalbach]

Works great thanks so much.

What would I need to do to display "Stat" above the date that it occurs. I am trying to utilize you "rHolidays" formula but would also like a visable indicator above the date that the stat occurs.

Also, I am going to utilize this for a biweekly timesheet...I would like to have some totals for each week (Week 1 & Week 2) populate a summary sheet, each week would be a new row. Essentially I would have 26 worksheets, with 2 weeks on each sheet that I want to populate to a summary sheet. One problem I see is the totals row can change location on the worksheet dependent on how may employees are in the timesheet for each week.

 

[Dave Patton]

I illustrate two possibilities for putting "Stat" above the date; you can use either one.
Copy the formula across all the relevant columns.

How you recap the time sheet depends in part on how the data is on the spreadsheet.

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M
1	Week	Stat	Stat
2		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total	Reg	Overtime
3		5	5	7	13	13	8	10	61	24	37
4
5
6	T201901a	1aaa
1aaa

Cell Formulas
Range	Formula
D1	=IF(COUNTIF(rHolidays,D2),"Stat","")
E1	=IF(MATCH(E2,rHolidays,0),"Stat","")
K3	=SUM(D3:J3)
L3	=MIN(40-SUMPRODUCT(--(rHolidays=D2:J2))*8,SUM(D3:J3)-SUMPRODUCT(--(D3:J3>8),D3:J3-8))
M3	=K3-L3

Named Ranges
Name	Refers To	Cells
rHolidays	='1aaa'!$O$2:$O$22

 

[bhalbach]

That worked...

I was thinking of a setup as below..

				Stat								1.5				5.77%	5.10%	1.62%		2.27%	4.00%	2.60%
	Name	Rate	17-Feb-19	18-Feb-19	19-Feb-19	20-Feb-19	21-Feb-19	22-Feb-19	23-Feb-19	Total	Reg	Overtime	OT %	Regular	Overtime	Vacation	CPP	EI Employee	Employee Net	EI Employer	Stat	WCB	Gross Cost	Cost/hr
			Sun	Mon	Tue	Wed	Thu	Fri	Sat
	Joe	$ 10.00	1.00		16.00	16.00	16.00	8.00		57.00	32.00	25.00	43.86%	$ 320.00	$ 375.00	$ 40.10	$ 35.45	$ 11.26	$ 648.30	$ 15.76	$ 12.80	$ 18.07	$ 817.18	$ 14.34
	Fred	$ 15.00	1.00	8.00	16.00	16.00	16.00	16.00	16.00	89.00	32.00	57.00	64.04%	$ 480.00	$ 1,282.50	$ 101.70	$ 89.89	$ 28.55	$ 1,644.06	$ 39.97	$ 19.20	$ 45.83	$ 2,059.08	$ 23.14
										-	-	-	0.00%	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
										-	-	-	0.00%	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
										-	-	-	0.00%	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
										-	-	-	0.00%	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
										-	-	-	0.00%	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
	Totals Week 1		2.00	8.00	32.00	32.00	32.00	24.00	16.00	146.00	64.00	82.00	56.16%	$ 800.00	$ 1,657.50	$ 141.80	$ 125.33	$ 39.81	$ 2,292.36	$ 55.74	$ 32.00	$ 63.90	$ 2,876.26	$ 19.70
												1.5				5.77%	5.10%	1.62%		2.27%	4.00%	2.60%
	Name	Rate	24-Feb-19	25-Feb-19	26-Feb-19	27-Feb-19	28-Feb-19	1-Mar-19	2-Mar-19	Total	Reg	Overtime	OT %	Regular	Overtime	Vacation	CPP	EI Employee	Employee Net	EI Employer	Stat	WCB	Gross Cost	Cost/hr
			Sun	Mon	Tue	Wed	Thu	Fri	Sat
	Joe	$ 10.00	-	8.00	8.00	8.00	8.00	16.00	-	48.00	32.00	16.00	33.33%	$ 320.00	$ 240.00	$ 32.31	$ 28.56	$ 9.07	$ 522.37	$ 12.70	$ 12.80	$ 14.56	$ 660.93	$ 13.77
	Fred	$ 15.00	-	8.00	8.00	8.00	8.00	8.00	8.00	48.00	32.00	16.00	33.33%	$ 480.00	$ 360.00	$ 48.47	$ 42.84	$ 13.61	$ 783.55	$ 19.05	$ 19.20	$ 21.84	$ 991.40	$ 20.65
										-	-	-	0.00%	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
										-	-	-	0.00%	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
										-	-	-	0.00%	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
										-	-	-	0.00%	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
										-	-	-	0.00%	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
	Totals Week 2		-	16.00	16.00	16.00	16.00	24.00	8.00	96.00	64.00	32.00	33.33%	$ 800.00	$ 600.00	$ 80.78	$ 71.40	$ 22.68	$ 1,305.92	$ 31.75	$ 32.00	$ 36.40	$ 1,652.33	$ 17.21

<colgroup><col><col><col><col span="7"><col span="3"><col><col span="2"><col span="3"><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2			Week		Stat
3	Name		17-Feb-19	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Total	Reg	Overtime	Rate	Total
4	Joe		17-Feb-19	1		16	16	16	8		57	32	25	$10.00	$695.00
5	Fred		17-Feb-19	1	8	16	16	16	16	16	89	32	57	$15.00	$1,762.50
6
7			24-Feb-19	Sun	Mon	Tue	Wed	Thu	Fri	Sat
8	Joe		24-Feb-19	1		16	16	16	8		57	33	24	$10.00	$690.00
9	Fred		24-Feb-19	1	8	16	16	16	16	16	89	40	49	$15.00	$1,702.50
Time

Cell Formulas
Range	Formula
D2	=IF(COUNTIF(rHolidays,D3),"Stat","")
D3	=$C3+(COLUMN()-4)
E2	=IF(MATCH(E3,rHolidays,0),"Stat","")
K5	=SUM(D5:J5)
K4	=SUM(D4:J4)
K8	=SUM(D8:J8)
K9	=SUM(D9:J9)
L4	=MIN(40-SUMPRODUCT(--(rHolidays=D$3:J$3))*8,SUM(D4:J4)-SUMPRODUCT(--(D4:J4>8),D4:J4-8))
L8	=MIN(40-SUMPRODUCT(--(rHolidays=D$7:J$7))*8,SUM(D8:J8)-SUMPRODUCT(--(D8:J8>8),D8:J8-8))
M4	=K4-L4
M5	=K5-L5
O4	=L4*N4+M4*1.5*N4
O5	=L5*N5+M5*1.5*N5

Named Ranges
Name	Refers To	Cells
rHolidays	=Time!$Q$3:$Q$25

Excel 2010
	A	B	C	D	E
1			Week of
2	Name		Start	End	Amount
3	Joe		17-Feb-19	24-Feb-19	$1,385.00
4	Fred		17-Feb-19	24-Feb-19	$3,465.00
5
Summary_Time

Cell Formulas
Range	Formula
E3	=SUMIFS(Time!O:O,Time!A:A,A3,Time!C:C,">="&C3,Time!C:C,"<="&D3)