How do I fix incorrect overtime calculation in employee payroll spreadsheet?

Question

Level 1

8 points

How do I fix incorrect overtime calculation in employee payroll spreadsheet?

Ok so a while back I made a post asking for some help on creating a payroll numbers spreadsheet and I received some help on creating it and getting it to work. After that Life got a little busy and It fell to the back burner to get it finished...now I have picked it up again. Here is the original thread if it is needed: Creating numbers sheet for timesheet and … - Apple Community

Now I have ran into another issue that I'm not sure how to fix. So I'm asking for assistance again. When I enter in all the times it is counting times as overtime that should not be overtime. So unless I make the overtime pay the same regular pay, my total pay is incorrect. The total pay is not respecting the week number so it's just adding in all the time together.

Hopefully someone more knowledgeable than myself can assist me in this regard. Thanks so much to anyone that reads and takes the time to assist with this!

[Re-Titled by Moderator]

MacBook Air 13″, macOS 15.3

Posted on Feb 16, 2025 5:48 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,426 points

Feb 17, 2025 3:40 PM in response to harcormor

The original template is for multiple people for one week/period of work at a time. If I understand your goal, you want it to be for one person (you) over a multi-week period. My answers below are based on that.

I asked what triggers overtime because if it is >8hrs in one day then it is one solution If it is >40hrs per week it is a different one. What the spreadsheet does now is unimportant. I am asking how you are going to determine overtime in this business you want this spreadsheet for.

If it is >8 hours in a day then it can be done easily in the sign in table. One column for regular pay, one for OT. Easy formulas. The summary will sum each column.

If it is >40 hours per week then you will need multiple rows in the summary table, one for each week. The original template had employee names listed in column A of the summary table. You will have the week numbers listed instead. Each row will add up the hours for that week number instead of employee name. Column A of the Sign In table will also be the week numbers, not employee name. With only one employee you don't need a column for employee name anyway. Alternatively you can keep the week number where you have it in the Sign In table and, in the formulas, change the reference from Sign In::$A to Sign In::$C (or Sign In::$B if you delete the column for employee name).

If there are to be multiple employees, the >8 hr OT solution works nearly the same. The >40 OT solution will get more complicated and I'm not sure how I would approach it.

Any of that make sense?

Reply

Answer 2

Badunit

Level 7

30,426 points

Feb 16, 2025 8:31 PM in response to harcormor

What is your formula in the "pay" column?

What triggers overtime? Anything over 8hr in a day?

What are the formulas in the summary table?

At first glance I'd say the sign in table could use columns for regular pay and overtime pay for each day. I'm not sure it needs a column for the total pay for each day. But if your existing column calculates the total pay (regular+overtime) correctly you may be able to use some math to break it apart in your summary table.

Reply

Answer 3

harcormor Author

Level 1

8 points

Feb 17, 2025 2:37 PM in response to Badunit

All of the formulas are the original formulas from the "employee timesheet" template in Numbers, plus the changes which that were made using the assistance from my original discussion(see the link in the original post)

The Pay column is just multiplying the hrs by 20 which is my hourly rate.

Overtime is triggered, to my knowledge, by the smaller table above the summary table, which sets "normal" hrs to 40hrs/week. the problem is it is counting all these entries as though it were one week, so as soon as it hit 40hrs its counting it all as overtime.

The formulas in the summary table are as follows:

Total Hrs: DURATION(0)+SUMIF(Sign In::$A,A2,Hours)

Regular Pay: IFERROR(IF(Total Hours Test≤Administration::B$2,DUR2HOURS(Total Hours Test)×Hourly Rate Test,Administration::$B$2×Hourly Rate Test),0)

Overtime Pay: IFERROR(IF(Total Hours Test≤Administration::B$2,0,(Total Hours Test−Administration::B$2)×(Hourly Rate Test×Administration::C$2)),0)

Total: Regular Pay Test+'Overtime ' Test

Hopefully there can be some sense made from all that...I just copy and pasted the formulas that were there...

Thank you for taking the time to look at this...

I wish there was a way to tag Camelot from the original post I made and they helped me out with it.

Reply