Solved

# Excel formula to calculate ovetime, trying to create simple timesheet

Posted on 2010-11-29
578 Views
Last Modified: 2012-05-10
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
Question by:IT-Medic
6 Comments

LVL 9

Expert Comment

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

Author Comment

@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

LVL 9

Expert Comment

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

LVL 9

Expert Comment

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

LVL 37

Expert Comment

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

LVL 9

Accepted Solution

felixdsouza earned 500 total points
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

## Join & Write a Comment Already a member? Login.

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity anâ€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

#### 772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!