Splitting Hours into Shifts using Data Validation / OT Rows

n0esc

New Member
Joined
Dec 19, 2005
Messages
15
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
ABCDEFGHIJKL
2PPE:Shift 1Shift 2Shift 3
3DateDayINOUTINOUTRegOTRegOTRegOT
412/12M0
512/13T0
612/14W23:304:004:308:008
712/15Th23:304:004:308:008
812/16F0:004:305:008:308
912/17Sa0:004:305:008:308
1012/18Su0
11TOTALS0000320
Sheet2
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

pfarmer

Well-known Member
Joined
Jul 6, 2005
Messages
550
Re: Splitting Hours into Shifts using Data Validation / OT R

n0esc said:
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

New Member
Joined
Dec 19, 2005
Messages
15
The start day for the work week will always be Monday and shifts and or hours wouldn't cross over pay periods.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,228
Messages
5,571,012
Members
412,353
Latest member
SofiaV
Top