• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

Excel formula to calculate ovetime, trying to create simple timesheet

Hello everyone,

I am working to create a spreadsheet that I can use to enter all our employees time data from their paper timecards. I know this is possible, but am having difficulty calulating the overtime. I attached the sample I am working with to show what I am looking for.. The sheet is pretty basic and I will just have a list of employees (colum A), a colum for hours worked last pay week of previous time period (colum B), weekly hours for the month (Colums C-G), a colum that calulates total straight pay (less than 40 hours) for the month (colum J), a colum that calculates total overtime for the month (colum K), and a total hours calulation (colum L).

Pay week is Monday-Sunday. Anything over 40 hours in the week is overtime.
Due to how our monthly pay periods work being 26th-25th (ie. Nov 26th-Dec 25th) it gets tricky with only the first weeks overtime calc. If an employee worked say 20 hours the last week of the previous pay period, and 40 hours in the first week of the new period, they would have 20 Regular hours and 40 Overtime hours.. This is where I am stumped how to get the sheet to properly calculate. Any help would be greatly apprecieated! Timecard-Sample.xlsx Timecard-Sample.xlsx
0
IT-Medic
Asked:
IT-Medic
1 Solution
 
jkunreinCommented:
It looks like you have the If statements working out pretty well.

Why not just put =SUM(C2:H2) - J2 for the overtime hours?
0
 
IT-MedicAuthor Commented:
@jkunrein: It still won't work for the hours in Week 1... thats my biggest hang up.. if the employee has worked 20 hours from the last week say of the January pay period, and 40 hours in Week 1 of February's pay period, I need to have it figure out that is 20 REG hours and 20 OT hours, instead of just 40 REG hours.. They have already been paid for the hours in colum B, but have to track because once they hit 40 hours its time and a half, hope that makes sense... Eventually I will have multiple sheets, one for each month and that field will be calulated from the previous sheet, but thats a ways down the line... Thanks!!
0
 
jkunreinCommented:
Ah, so you are meaning that column B and column C are in the same week? Different pay periods, but within the same calendar week?

So, both weeks should have 40 hours of overtime?

I just want to make sure I understand.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jkunreinCommented:
Oops, I just saw that Week 1 should have 20 REG and 20 OT. Actually, I'm afraid I do not wholly follow. Could you post examples of what column K should have? In addition to John and Jane Doe, throw some more examples. The more examples there are, the more refined the formula will be.

Sadly, I have to head out. If nobody else tackles this problem, I'll be glad to take a look at your examples.
0
 
TommySzalapskiCommented:
Should that first chunk of your big Regular Hours function just be IF(C2+B2>40,40,C2+B2)? Making the whole thing be...
=SUM(IF(C2+B2>40,40,C2+B2),IF(D2>40,40,D2),IF(E2>40,40,E2),IF(F2>40,40,F2),IF(G2>40,40,G2),IF(H2>40,40,H2))
0
 
felixdsouzaCommented:
I think the formulae in the attached sheet should do the trick.  Note I have changed all the formulae - for Regular Hours, OT Hours and Total Hours. Overtime-Calculation-Formula.xls
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now