Project Task Tracking Payroll formula help

**japavano** · 10-04-2013, 03:37 PM

I am trying to create a weekly Labor tracking spreadsheet for numerous employees and various tasks. We pay weekly based on a 40 hour week, so any hours more than that are overtime at time-and-a-half. I've attached a sample of what I've done so far, but the formulas for the individual tasks do not calculate properly, which is why I need HELP with this effort.

I want the individual tasks to total for the week for each employee. The formula in the worksheet is not currently calculating as I need it to, something wrong in my logic. Once calculated properly I will copy the costs to a master worksheet for job costing.

I also want the total hours for each employee to calculate proper regular and overtime pay. That formula seems to work as it should.

Any help will be GREATLY appreciated!

**Greg M** · 10-05-2013, 02:25 PM

Hello there!

I think you have a more fundamental problem than formulas not calculating correctly - your data are not recorded in a way which allows you to determine the result you are looking for!

Suppose that on Monday, Frank works for nine hours on Feeders and then works for three hours on Testing - i.e. twelve hours in total - eight hours at Basic rate on Feeders, one hour at Overtime rate on Feeders, and three hours at Overtime rate on Testing.

He then repeats the above procedure each day from Tuesday to Friday.

At the end of the week Frank will have worked for a total of 60 hours. This is made up of 45 hours work on Feeders (40 hours at Basic rate and 5 hours at Overtime rate) plus fifteen hours on Testing (all at Overtime rate).

Now, the task-related formulas (in Columns I and J) on your worksheet will correctly record the five overtime hours worked on Feeders (because the total time worked on Feeders exceeds 40 hours) but the fifteen hours of overtime worked on Testing will be recorded as BASIC hours (because the total time worked on Testing does not exceed 40 hours)!

The day-related formulas (on Row 27) and the overall total formula (on Row 28) will correctly identify the daily and overall totals, but the overall total payout will NOT be the same as the sum of all the task-related payouts if any overtime hours have been worked.

This also means that the task-related payouts shown on the Summary worksheet cannot be calculated accurately.

Maybe the above is bad news for you, or maybe I've totally misunderstood the situation - in either event, I'm sorry

Have I misunderstood? Is the only problem the fact that your formulas are producing #REF! errors? Let me know and I'll see what I can do.

Regards,

Greg M