Daily and Weekly Overtime formula

**Sarah in Canada** · 11-22-2011, 02:00 PM

Hi everyone - I just joined today, as I have come to the conclusion that I am wasting my time and should just ask for help from all of you.

I am trying to create a formula to check the compliance to an overtime policy. Per the contract, the work week runs from Monday to Sunday, and the regular work week is 10 hours per day, Mon - Thursday, 40 hours per week. Anything over 10 hours per day or 40 hours per week would be overtime. I have 3 years of data to analyze, with hundreds of employees, and I need to figure out the amount of overtime that should not have been paid for each employee, for each week. UGH.

The data that I have been provided is in the attached format document after being sanitized for all relevant proprietary and private information of course!)

I was thinking that as the overtime needs to reset each week, that I need to have a weeknum in there somewhere, but I am quite lost as to how to tackle this!

Any assistance would be greatly appreciated!!

**ConneXionLost** · 11-22-2011, 03:15 PM

How does the contract deal with these scenarios:

a) Employee works four days in a week at: 10, 8, 12, 10 hours.

Assumed as 2 hours overtime?

b) Employee works four days in a week at: 10, 10, 12, 10 hours.

Assumed as 2 hours or 4 hours overtime?

Cheers,

**Sarah in Canada** · 11-22-2011, 04:03 PM

In both scenarios, it would be 2 hours of overtime.

The wording of the contract is:

Overtime rates are payable for all hours worked onsite (excluding travel time) in excess of 10 hours per day or 40 hours per week, with the 'week' beginning on Monday of any calendar week, and for all hours worked on statutory (as designated by Alberta Labour Relations) holidays.

Thanks for looking at this!!

**ConneXionLost** · 11-23-2011, 07:15 PM

Hi Sarah,

This may not be the most elegant approach, but...

Please see attachment.

What you asked for is a bit beyond just a formula. Also, you mentioned "hundreds of employees", if that is beyond 250 (Excel 2003 column limitation), then you'll need to do this in batches.

I primarily used Pivot tables and the WEEKNUM function (from the Analysis Toolpack) to get a result.

Sheet1 - your data (starting point)

Sheet2 - Pivot table to consolidate the info so that we get one row per day with one Employee per column. Table contains a sum of all activity hours. Also added a calculated field for ContractDailyOvertime:

=MAX(ACTIVITY_HOURS-10,0)

Sheet3 - Pivot table (copy from Sheet2) changed to display the sum of the ContractDailyOvertime.

Sheet4 - Copy of Sheet3 (removed Pivot functionality), and added a column for YearWeek:

=YEAR(B5)&WEEKNUM(B5,2)

Also built a summary table to collect the weekly sum of ContractDailyOvertime. I used a SUMIF function for this:

=SUMIF($A$5:$A$24,$A31,C$5:C$24)

Sheet5 - Identical to Sheet4 except it uses Sheet2 data, and sums the difference of the ActivityHours versus a 40 hour week to see the weekly overtime as per contract.

Sheet6 - Summary table comparison between Sheet4 and Sheet5. My assumption as to how you calculate overall overtime (daily vs weekly) as per the contract.

Sheet7 - Another Pivot table to sum the overtime originally paid.

Sheet8 - Original overtime summed by the week.

Sheet9 - Overtime paid but not according to contract.

Cheers,