calculating hours worked on shift

K

keithcuda

New Member
Joined
Jan 15, 2013
Messages
29
I'm thinking this is a pretty easy one.

I am doing a new schedule at work and if the total number of hours in a work week are greater than 80, then it will need to be calculated with overtime.

basically what I am trying to think of is; the result is 84, then 80*x=y, then the 4 remaining hours would be 4*1.5x=w, then add them together to get a total, y+w=total earnings

anything over 80 would be time and one half.

I am using this formula to count the days worked, 1 being first shift and 2 being second shift; =(SUM(COUNTIF(I13:V13,{1,2}))*12)
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
The 12 would be hours worked in a day, 12 hour shift. 3 on, 3 off for a two week pay period would put you at 84 hours.
 
Upvote 0
Didn't you say total number of hours in a week? Does nobody ever do overtime on a workday ie 14 hour shift for example?
 
Upvote 0
There will be an "X" representing a day off.

i was using, =(SUM(COUNTIF(I13:V13,{1,2}))*12)*A3
A3 would contain your hourly wage. But it wont be accurate since there is 4 hours of overtime.
 
Upvote 0
If normal hrs are 80 per week, will you need to split the countif, so if they work 84 in one week, then 76 in 2nd week, will that be 4hrs O/T and 156hrs normal?
 
Upvote 0
I must of made a slight mistake. It would be 84 hours in a pay period. a pay period consist of two weeks. The total hours in the pay period would be 84, from which i got from =(SUM(COUNTIF(I13:V13,{1,2}))*12)


anything over 80 hours I will need to multiply 1.5 times the base wage

so 84 hours
80*x=y
(4*x)*1.5=w
y+w=total earnings
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

alm395
  • Question
Coverage By Hour
Replies
3
Views
152
alm395
alm395
J
  • Question
Shift bands
2 3
Replies
29
Views
956
felixstraube
F
W
  • Question
Holiday hours Calculation
Replies
2
Views
385
Wrenn_6869
W
M
  • Question
Splitting AM and PM hours from a Date Range
Replies
4
Views
313
mcmuney
M
Ramballah
  • Question
Huge nested IF or is there a simpler way?
Replies
4
Views
173
Ramballah
Ramballah

Forum statistics

Threads
1,214,429
Messages
6,119,435
Members
448,898
Latest member
dukenia71

Share this page

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
Back
Top