calculating hours worked on shift

[keithcuda]

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)

 

[keithcuda]

Gaz im tracking you now...

two week pay period. the first week would be 48 hours and the second would be 36. So actually it would be 8 hours of overtime, because anything over 40 is time and a half. sorry, its 3am here

 

[keithcuda]

I would like one value for both. a formula that will come up with the exact amount. No need to split them

 

[gaz_chops]

Test this

=MIN(40,COUNT(I13:O13)+MIN(40,COUNT(P13:V13))*12)*A3+MAX(0,COUNT(I13:O13)*12-40+MAX(0,COUNT(P13:V13))*12-40)*A3*1.5

 

[keithcuda]

Gaz something doesnt seem right.

lets say A3 = 10
for the first week totaling 48 hours, that would equal = 520
for the second week totaling 36 hours, that would equal= 360
totaling 880

Your formula came up with 460...

 

[gaz_chops]

Sorry made a mistake with the parentheses

=(MIN(40,COUNT(I13:O13)*12)+MIN(40,COUNT(P13:V13)*12))*A3+(MAX(0,COUNT(I13:O13)*12-40)+MAX(0,COUNT(P13:V13)*12-40))*A3*1.5

 

[keithcuda]

That seems to work a lot better!!! Thank you so much.

Now if i only knew what it was doing, so if i needed to change it later i could

Any chance you could break that down to the 3rd grade level and try to explain to me what is going on?


Cheers

 

[gaz_chops]

OK I repeated the same formula twice to cater for the 2 weeks each having normal hours of 40.
Count is doing the same as your countif (note count only counts numbers).
Min will return the smallest number between 40 and the count X 12, so if over 40 it will return 40.
Repeat for 2nd weeks then add together and X A3
Then calculate the O/T for each week
Max will return the largest number between 0 and the count x12 minus 40

=(MIN(40,COUNT(I13:O13)*12)+MIN(40,COUNT(P13:V13)*12))*A3+(MAX(0,COUNT(I13:O13)*12-40)+MAX(0,COUNT(P13:V13)*12-40))*A3*1.5

Hope that makes sense!!

Gaz

 

[keithcuda]

Perfect sense now! Thanks again!!

 

[gaz_chops]

You're welcome.