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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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,123,237
Messages
5,600,475
Members
414,383
Latest member
konmtu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top