Calculating overtime once employee has accumulated 40 hours.

I need help calculating overtime once an employee has worked over 40 hours. I would like to create a formula that calculates the weeks regular hours worked and then once that employee has reached 40 hours begin calculating this overtime. Another factor I need to formulate is overtime by department. We have 2 departments that employees work in throughout the week. For example if an employee has worked in department A 32 hours that week and deparment B for 12 I would need to create a formula so it recognizes when 40 hours are worked and would then seperate this into overtime by department if applicable.

Can you upload an example?

Assuming the total number of hours worked is in cell A1, the formula to calculate overtime hours is:

=IF(A1>40,A1-40,0)

(If the total hours worked is greater than 40, then the overtime equals the total hours worked minus 40, otherwise the overtime equals zero)


If you have the total hours spread out over multiple cells, like you describe in the case where you have the employee working in multiple departments, you just need to find the sum of all those hours, and then use the same formula above.

So, if cells A1 and B1 have the hours worked in each department,

=IF(A1+B1>40, A1+B1-40, 0)


In your example, you also implied that you want the formula to tell you what department the overtime was worked in. If that's the case, you would need to be more specific with how you would normally calculate this.

In the example you gave, the employee worked in Dept A for 32 hours, and Dept B for 12 hours. Given only that information, I have no way of telling you which department the overtime was worked in.


--
Marc.

You could also do this:

=IF((A2+B2)<=40,(A2+B2)*C2,40*C2+((A2+B2)-40)*C2*1.5)


Column A = Hours worked in Department A
Column B = Hours worked in Department B
Column C = Regular Pay rate

The formula says:

If Total hours worked are 40 or less, do the total times rate
Otherwise (over 40), do 40 times rate, plus the hours above 40, times the payrate, times 1.5 (which is the rate for overtime, but you can change it to whatever rate applies to your business.

Hope this helps.

Originally Posted by fleury

Assuming the total number of hours worked is in cell A1, the formula to calculate overtime hours is:

=IF(A1>40,A1-40,0)

(If the total hours worked is greater than 40, then the overtime equals the total hours worked minus 40, otherwise the overtime equals zero)


If you have the total hours spread out over multiple cells, like you describe in the case where you have the employee working in multiple departments, you just need to find the sum of all those hours, and then use the same formula above.

So, if cells A1 and B1 have the hours worked in each department,

=IF(A1+B1>40, A1+B1-40, 0)


In your example, you also implied that you want the formula to tell you what department the overtime was worked in. If that's the case, you would need to be more specific with how you would normally calculate this.

In the example you gave, the employee worked in Dept A for 32 hours, and Dept B for 12 hours. Given only that information, I have no way of telling you which department the overtime was worked in.


--
Marc.

Marc--Thanks for all your help so far. To get a little more specific let's say on Thursday at the end of the day they had worked a total of 36 hours between both departments for the week so far. On Friday they worked 7 hours so at the end of the day they had a total of 42 hours worked for the week (they worked 5 hours on Friday in deparment A and then worked 2 hours on Friday in deparment). I need the formula to recognize that at 4 hours into Friday they began the overtime. In this case the formula would need to calculate 1 hour of over time for department A for that week and 2 hours of overtime in deparment B. Hopes this helps.

Can you upload an example of how the work hours are tracked? And include a sample of what the output should look like. It will make it easier to work out what the forumlas should look like.


--
Marc.