Calculate Regular and Overtime Hours

[arsallee3]

Good day Community,

Have a problem that have not been able to solve. Given the below data I would like to calculate the regular time hours & overtime hours for each team member by Region,Week #. and I have just been stumped.

Appreciate any thoughts as probably making this tougher than should be.

Thanks

Data feed     

EECode	Region	Week #	DayEarnHours
0379	Eagle	1	17.68
0379	Eagle	1	17.23
1082	Midcon	1	15.03
1082	Midcon	1	15.58
0379	Eagle	1	17.05
1082	Midcon	1	15.13
0370	Eagle	1	17.33
1082	Midcon	1	15
0370	Eagle	1	17.27
0370	Eagle	1	8.87
1082	Midcon	2	0.93
1082	Midcon	2	15.13
1082	Midcon	2	14.85
0379	Eagle	2	14.95
0379	Eagle	2	14.82

Desired result in pivot table

Region	Week	EECode	Total Hours	Regular Time	Overtime
Eagle			125.2	109.77	15.43
	1		95.43	80	15.43
		0370	51.96	40	11.96
		0379	43.47	40	3.47
	2		29.77	29.77	0.00
		0379	29.77	29.77	0.00
Midcon			91.65	70.91	20.74
	1		60.74	40	20.74
		1082	60.74	40	20.74
	2		30.91	30.91	0.00
		1082	30.91	30.91	0.00
Grand Total			216.85	180.68	36.17

[sunny_talwar · Accepted solution]

This?

[arsallee3]

That is perfect Sunny.

Just for my edification, if I wanted to add or reduce the pivot summary just add or reduce the Aggr name summary at the end of the statement. Would that be correct?

[sunny_talwar]

Well it would depend... isn't that straight forward.... you need to aggregate on the lowest level to make sure your check for < or > 40 hours is still valid... if you do it Region and Week #, removing EECode, you might not get the results you desired.... but I guess play around with it to see what works for you....