Solved

Excel formula to calculate ovetime, trying to create simple timesheet

Posted on 2010-11-29
6
585 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
Comment
Question by:IT-Medic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 9

Expert Comment

by:jkunrein
ID: 34233297
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

by:IT-Medic
ID: 34233433
@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

by:jkunrein
ID: 34233451
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
Technology Partners: 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!

 
LVL 9

Expert Comment

by:jkunrein
ID: 34233476
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

by:TommySzalapski
ID: 34233822
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

by:
felixdsouza earned 500 total points
ID: 34308936
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

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

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

Join & Ask a Question