Solved

Excel formula to calculate ovetime, trying to create simple timesheet

Posted on 2010-11-29
6
582 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
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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 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