Nov 19, 2012

I'm working on a dynamic payroll spreadsheet that will automatically calculate the overtime worked in a week. Right now, I'm running into a snag. My issue is with the formula in Column R. Right now, as shown below, it is doing the calculation based on regular hours minus 40 to determine the OT time. The snag is very messy and it lay in this: while the row by row calculations for total overtime worked for the week is correct, the sum at the bottom is very much off. I need an accurate method to sum the hours of overtime for the given column.

Here are the guidelines for the pay periods and overtime:

1. The pay periods for the month go from the 1st to the 15th and the 16th to EOM (End of Month). This means that the pay period could end on any given day of the week. More on this in a moment.

2. A work week is defined as Sunday to Saturday.

3. Overtime is calculated based on the rule of anything over 40 hours in a given work week.

4. Holiday hours worked do not count towards the 40 hour mark in granting overtime since Holiday pay is automatically overtime.

If it were just a matter of a bi-weekly (every 2 weeks) pay period, I would simply state =IF(weekday(DATE)=7,Hours_Worked - 40,0), and tag a SUM(range) at the bottom. Unfortunately, with it being a semi-monthly (twice a month), the end of the pay period could be a Wednesday, so a reference to day of the week won't work unless the formula can dynamically determine which set of data to evaluate.

I'm completely willing to toss out the current method of determining overtime. This is the calculations sheet that references a cleanly formatted and designed time card on a tab called "Time Card", so this isn't the full workbook. In fact, once the whole thing is done, this calculation sheet will be hidden.

Columns M and N (which are formula referenced in Column P) are basic End - Start calculations and were hidden to simplify the display as well as the number of formulas displayed.

Column L (formula referenced) is a Yes/No display for if the date in question is holiday pay.

Excel 2003

H

I

O

P

Q

R

S

1

Start Work

Time Out

Day Count

[code].....

View 3 Replies
View Related