Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel formula to calculate ovetime, trying to create simple timesheet

Posted on 2010-11-29
6
Medium Priority
?
591 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
Industry Leaders: 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 2000 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

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.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 the scrolling table in Microsoft Excel using the INDEX function.

715 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