Splitting Hours into Shifts using Data Validation / OT Rows

[n0esc]

Here is my problem. I am using a Timesheet given to me by my companies payroll dept. It is bi-weekly and we are expected to manually enter our hors and then self total the worked hours into the appropriate columns. I have started by setting up data validation for the shifts our dept uses for the IN and OUT times and the formula to total the hours into the 3rd shift column for myself.

I am stuck on working out a formula to insert the hours worked into the appropriate shift column depending on the selections for IN and OUT if that is possible.

I would also like to be able to total the OT column as well. I have not found anything through search that quite fits the way our payroll dept has it laid out on this sheet. Per MN laws we are paid for anything over 40. I have put in formulas in K11 K23 and K29 to populate the adjacent OT total, but cannot work out how to populate a particular days cell once 40 hours in a week have been worked. I could work 24hr on, 24 off, 24 on, and only 8 hours of the 3rd day would count as OT.

K11 is totaled with K19 using =IF(SUM(K11,K19)>80,80,SUM(K11,K19)) and K20 populated by =IF(SUM(K11,K19)>80,SUM(K11:K19)-80,"0") which gives me total regular hours and total OT hours in the 2 week period, but leaves me stuck for how to get K4:K10 into the OT column once I exceed 40 hours.

Sorry for being long winded.

Timesheetprogress.xls
	A	B	C	D	E	F	G	H	I	J	K	L
2					PPE:		Shift 1		Shift 2		Shift 3
3	Date	Day	IN	OUT	IN	OUT	Reg	OT	Reg	OT	Reg	OT
4	12/12	M									0
5	12/13	T									0
6	12/14	W	23:30	4:00	4:30	8:00					8
7	12/15	Th	23:30	4:00	4:30	8:00					8
8	12/16	F	0:00	4:30	5:00	8:30					8
9	12/17	Sa	0:00	4:30	5:00	8:30					8
10	12/18	Su									0
11	TOTALS						0	0	0	0	32	0
Sheet2

 

[pfarmer]

Re: Splitting Hours into Shifts using Data Validation / OT R

Here is my problem. I am using a Timesheet given to me by my companies payroll dept. It is bi-weekly and we are expected to manually enter our hors and then self total the worked hours into the appropriate columns. I have started by setting up data validation for the shifts our dept uses for the IN and OUT times and the formula to total the hours into the 3rd shift column for myself.

I am stuck on working out a formula to insert the hours worked into the appropriate shift column depending on the selections for IN and OUT if that is possible.

I would also like to be able to total the OT column as well. I have not found anything through search that quite fits the way our payroll dept has it laid out on this sheet. Per MN laws we are paid for anything over 40. I have put in formulas in K11 K23 and K29 to populate the adjacent OT total, but cannot work out how to populate a particular days cell once 40 hours in a week have been worked. I could work 24hr on, 24 off, 24 on, and only 8 hours of the 3rd day would count as OT.

K11 is totaled with K19 using =IF(SUM(K11,K19)>80,80,SUM(K11,K19)) and K20 populated by =IF(SUM(K11,K19)>80,SUM(K11:K19)-80,"0") which gives me total regular hours and total OT hours in the 2 week period, but leaves me stuck for how to get K4:K10 into the OT column once I exceed 40 hours.

Sorry for being long winded.

A couple of questions.

You mentioned MN laws. You sheet shows a particular start day, will this always be the same? Is the 40 hour straight time a work week or a running week that would pass over the typical Saturday/Sunday or Sunday/Monday line that starts a new week?

Perry[/quote]

 

[n0esc]

The start day for the work week will always be Monday and shifts and or hours wouldn't cross over pay periods.