Calculate overtime based on 8 hour day / 44 hour week

Hello,

I have a working time sheet that we have been using for years and it functions very well. It is currently based on a 44 hour work week. I need to alter it so that it also takes into account the amount of hours worked in a day. It needs to calculate overtime after 8 hours in a day and when the cumulative time worked in the week exceeds 44 hours.

For example,

Joe Technician works 9 hours on Monday and then works 8 hours a day for the rest of the week. While he doesn't break the 44 hour threshold for the week he would still get paid 1 hour OT for the 9 hours he worked on the Monday. Conversely if he worked 10 hours a day for 5 days, by the 5th day he would get 4 hours of regular time and the remaining 6 would be paid as OT since he crossed the 44 hour threshold after 4 hours worked on the 5th day.

I've included examples in the worksheet that shows how the current formula works. I would like to keep the current functionality and add in the 8 hour calculation.

The formulas that I need to alter are located in cells E22:K22.

Thank you in advance.

forum.ozgrid.com/index.php?attachment/70898/

Re: Calculate overtime based on 8 hour day / 44 hour week

Quote from Infomage;782494

Let's make sure we're clear:

* Hours >8 in a single day to count as overtime for that day
* Hours >44 in any one week to count as overtime

So, does that mean that someone who works 10 hours on Monday then 6 hours per day for the remainder of the week would have a total of 34 hours, 2 of which are overtime?

Hello Infomage,

Yes that is correct. Overtime is earned after 8 hours on any single day as well as when you surpass 44 hours total in one week.

Re: Calculate overtime based on 8 hour day / 44 hour week

Infomage,

Thanks it works almost perfectly.

In testing I found one small issue. If the tech works 8.25 hours on one day he'll get .25 hours of OT which is correct. If his balance for the week ends up being 44.25 hours the sheet is calculating that he will get paid an additional .25 hours while he was already paid the .25 hours on the day that he worked 8.25. The formula needs to look at the daily amounts paid to ensure that it's not double paying. I've attached an example.forum.ozgrid.com/index.php?attachment/70907/

Re: Calculate overtime based on 8 hour day / 44 hour week

Hi Infomage,

Hope you feel better.

Sorry for the late response. Between work and the holiday season things have been crazy.

This seemed to be far more complex than it should be so I called the Employment Standards agency for this area and have confirmed the following.

Overtime is to be paid for hours worked daily in excess of 8 hours in a day or 44 hours in a week, whichever is greater.

This means if the tech worked 10 hours per day for 5 days they would get 10 hours of OT. 2 hours per day x 5 days is 10 hours of OT. 50 hours for the week subtract 44 hours is 6 hours of OT. 10 hours is greater than 6 so the tech will get 10 hours of OT.

Conversely if the tech worked 9 hours a day for 5 days and then 9 additional hours on Saturday they would get 10 hours of OT. Daily OT would only be 6 hours in total (1 hour per day x 6 days) where as the weekly total would equal 10 hours. 9 x 6=54 - 44 =10 hours OT.

I hope this clarifies it. Sorry for the confusion on my part as well as the extra work.

Re: Calculate overtime based on 8 hour day / 44 hour week

Hi Infomage,

That works perfectly. It's actually really close to an iteration of the formula that I attempted as well. Thank you so much for your efforts and patience with this. I know I kind of led us down the wrong path for a bit. Once again my apologies for that. Members like you are what makes this site so valuable. Thank you again and I hope that you have a great holiday season!!