Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Payroll Calculation With Overtime

We are working on a spreadsheet that would project what our labor cost would be for next week. I need some help in figuring out how to calculate overtime when an employee reaches 40 hours.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Overtime Calculation
Obviously this is not correct because the result is FALSE.

View Replies!   View Related
Calculation Of Overtime
I use Excel 2007, and I need help with an overtime calculator. It pertains to a specific wage order, which has three basic principles:

Any hours over 16 in one day are double-time. (2x)
Any hours over 40 in a week are time-and-a-half (1.5x)
Any hours over 48 in a week are double-time. (2x)

I worked 5 hours on a Monday, 18 hours on a Tuesday, 18 hours on a Wednesday, and 13 hours on a Thursday. (I work in a residential group home, so 24 hour shifts are common). That totals 54 hours, and the correct overtime breakdown should be:

40 regular hours.
8 hours at time-and-a-half, and
6 hours of double time.

Im using the following formulas: ...

View Replies!   View Related
Overtime Calculation On Timesheet
" =(C2 >D2)*MEDIAN(0,D2-1/4,1/2)+MAX(0,MIN(3/4,D2+(C2 >D2))-MAX(1/4,C2)) "

approach to sort out Day/Night Hours. Its bomb proof!

A new situation demands overtime payments......start and finish time can be any time day or night (crap job!), overtime is payable after 8 hours. Thus I have day (0600-1800) standard rate, day (0600-1800) overtime rate, night (1800-0600) standard rate, night (1800-0600) overtime rate.

So, starting at 1400 and finishing at 0100 give 4 hours day std + 4 hours std night + 3 hours night o/time; whereas starting at 0200 and finishing at 1300 gives 4 hours std night + 4 hours day std + 3 hours day o/time.

I'm using Excel 2003 and 2007 so use the Excel 97-2003 format.

View Replies!   View Related
Payroll :: Calculating These Formulas?
I am having a little trouble with IF. My pest control sales people are paid on straight commission. In addition to paying them a sliding percentage of the total contract value I pay them 50% of everything above $100 they charge on the initial service.

calculating these formulas?

Attached is the payroll document.

View Replies!   View Related
Function To Calculate Payroll
I am trying to calculate payroll. I have 2 columns where regular hours and overtime hours are manually inputed. They are then multiplied by their respective pay per hour columns to come up with regular and overtime pay. The next column adds these to get total gross pay. That gross pay is then multiplied by the FICA and FICA Med factors to figure those taxes(2 different columns). I then have a column the adds all deductions to get total deductions(Fed,FICA,FICAMed,St). The last column subtracts total deductions from the gross pay column to get net income. My problem is the net pay column is $.01 off sometimes.

I think what is happening is I obviously have all columns in dollar amounts with 2 decimal points. Some function columns have multiple decimal points in the answer and then are only showing the 2 decimal points. When those columns are used in the next equation, instead of using the dollar amount that is showing with 2 decimal points, it is remembering the multiple decimal points. This is resulting in being a penny off when I get to the end. How can I get the equations to use what actually shows in the columns(2 decimal points) instead of remembering multiple decimal places?

View Replies!   View Related
Payroll Extract To Work Only On Certain Pay Period Dates.
I currently have an Excel payroll extract that populates a start date and end date via the calender control 11 user form.

What I have found out is I need to inject some sanity to this application. I can not have users select days that are outside of a pay period.

The users should only be able to select the first or the 16th of the month for a pay period start. Then they can only select the 15th or the 28th/31st for the pay period end.

What I am trying to do is have them select the month and the pay period start date, then the end date would automaticly be selected. But I don't want to have to create a bunch of loops to counter for the differing month end dates or leap years.

View Replies!   View Related
Desperately Seeking Solution - Payroll Tax Withholding
I need to determine federal tax withholding from gross pay.

If gross amount (for S-0 = single no dependents) is
Over.................But NOT Over............Then Subtract.........And multiply
$0.00...............$195.00.....................$5 1.......................10%
$195.00............$645.00.....................$99 .......................15%
$645.00............$1,482.00..................$351 ......................20%
$1,482.00..........$3,131.00..................$447 ......................25%
$3,131.00..........$6,763.00..................$849 .....................30%

If gross is $400 then what are the total taxes taken out? Could someone please provide a formula?

I tried this one: =IF(M10

View Replies!   View Related
Calculating The Overtime
We are guaranteed (right now) a 10 hour day. (we are on 4 - 10 hour shifts). So if we work 9 hours say on Monday, we get 9 hours of straight time and an hour of short work week (approx 80% of pay). Now if we work 11 hours on Tuesday (which they can do unfortunately) I get 11 hours of straight time and no overtime. We have to make up for the short work week hour. So a less complicated explanation would be if I dont work more than 40 hours per week, no overtime no matter what I actually worked per day. Seems pretty simple but what I want and need to do is to calculate it per day.

Mon 10 Hours
Tues 11 Hours
Wed 9.5 Hours
Thurs 10 hours

I should get:

Mon 0 overtime
Tues .5 overtime
Wed 0 overtime
Thurs 0 overtime

I got it to the point where If the day where we get short work week is first and overtime after that, it works. But if we work overtime first then get short work week later in the week, it wont calculate it. I know why it wont work now but I dont know how to make it work. LOL Here's a link to the file.

View Replies!   View Related
Calculate Overtime
I have a time sheet for my employee's that I need to calculate their overtime in 1.5x and 2x rates.

Their overtime totals are done in individule columns from D33 to S33. The first 4 hours per day are charged at 1.5x and anything over that is 2x. I want to show the 1.5x in one box and the 2x in another. I do believe that I need two formulas one in each of the boxes where the final totals would go.

Here's an example, in columns D37 to D41 the employee has worked 12, 14, 9, 16 and 14.5 hours. so that's 17 hours @ 1.5x and 8.5 @ 2x.

View Replies!   View Related
How To Calculate Overtime
I am trying to formulate a formula that will calculate overtime hours worked.
Now standard hours are 17:30pm - 20:45pm. Anything outside these hours are overtime. If the start time is 18:00pm then the person is still paid from 17:30pm @ standard rate regardless.

Now I am trying to work out a formula that will cover hrs outside of the standard hrs AND hrs unworked but paid for.

see attached! September tab {blue highlighted cells}

View Replies!   View Related
Calculating Overtime
I'm looking to calculate OT wages when they happen vs only at the End of Week totals. ie... if the employee hits 40 hours midshift on a Wed, I want to calculate what the total dollars would be for Wed.... a few hours at regular time plus what ever hours above 40 at time and a half.

View Replies!   View Related
Sum Overtime Hours
I have a report given to me formatted as general. These are overtime hours for 5000+ associates. The time is shown as 4.52 being 4 hours and 52 minutes.

If I sum 4.52, 5.1, .18... I get 9.8 when in fact it is 10hrs 20m. I need this to display as 10.2

In fact I have done it in the past but lets just say im ready for the weekend.

View Replies!   View Related
Employee Timesheet With Overtime
i am creating a weekly time sheet for my company.the problem that i have is when the persons time reaches 40 hours, the time needs to be calculated in the overtime field. this is really tough for me when the person reaches 40 hours in the middle of the work day. I cant figure it out. i have attached the spreadsheet if you would like to look.

View Replies!   View Related
Counting Overtime With A Twist
Each employee has a different plan time each day of the week.
Mon - 9 Tues - 9 Wed - 7 Thur - 8 Fr - 7

Overtime is really counted after 40

There are 5 sheets for each day of the week mon-fri lets say sheet1..2...3...etc, on each sheet there is a column a with plan and column b with actual hrs. What I'm looking for is a summary sheet for each employee to see where they trend in OT after each day so...

person1 works 10 hrs on mon with a 9 hour plan thus 1 hr trending OT. On the summary page person1 would now show 1 hrs of OT. Now if they would work 9 hrs on tues it would still show 1 hr of OT. On wed however they worked 6 hrs so now the summary page would 0.

View Replies!   View Related
Calculate Overtime Hours
I am trying to get my time sheet to work out right but for some reason the formulas are a little more then what I can do. My time sheet is set up on a weekly bases. I have a regular time line, an overtime line and a total time line. an example I have is I work 12 hours a day I need an 8 to show up in the regular hours, 4 in the overtime and 12 in the total hours.

View Replies!   View Related
Track Hours Of Overtime Worked
I am creating a spreadsheet that will track hours of overtime worked and within the spreadsheet there are several separate departments listed.

I have made it dynamic so that the summary spreadsheet will update as employees are added. Im using a macro and some complicated helper cells to be able to sort the employees based on their total OT hours worked.

View Replies!   View Related
Overtime In Semi Monthly Timesheet
Im trying to take an existing employee time sheet in Excel (Office XP) that has no formulae whatsoever, and add the appropriate formulae so that all an employee needs to do is enter the daily start and end times and the time sheet will calculate daily, weekly, and overtime hours worked. Among others, some of the problems Im having are:

I need to keep the original format (though I've added a few columns).

Overtime in the State of Texas does not apply until after 40hrs have been worked. Then any daily hours over 8 can be applied retroactively. So I need a timesheet that shows overtime as regular hours worked until 40 hours have been reached, then separates the daily overtime from the regular column and places it in a daily overtime column. Shouldn't be too hard to find...Right?... Actually, thats been quite easyexcept for weekends. Saturdays and Sundays are usually overtime but not always.

The real problem is the beginning day of the pay period, if a pay period begins on any day other than Monday (Wednesday, for example,) then weeks one and sometimes three can never equal 40 hours each unless the assumption is that the days worked in the same week but prior or subsequent period are worked at 8 hours each. The formulae must make this assumption. How do I write a formula that assumes an empty cell actually has a value? :o

I know that its difficult (if not impossible) to offer any suggestions without seeing the time sheet itself, so, If it would be helpful, and anyone has any suggestions. Ive uploaded the week one of the timesheet as it stands now.

If you'd like to see the entire worksheet I've uploaded it to ....

View Replies!   View Related
How Can I Get Excel To Work Out My Hours And Overtime
I charge 45 per hour with a couple of conditions - minimum 8hr day and then 1.5T after 12hrs.

Is it possible to have a table where I put in my hours and then Excel tells me how much I should charge?

View Replies!   View Related
Argument Separate Overtime From Regular
spreadsheet has daily hours worked per employee and a weekly total. What is the formula to take the weekly total per employee and separate the regular hours (first 40) and have the overtime show up in the next column (without getting negative numbers for the totals under 40).

View Replies!   View Related
Calculate Timesheets Including Hours At Overtime
I'm trying to calculate timesheets including hours at overtime.
A1- Start time, B1- Lunch Start, C1- Lunch finish, D1- Finish
In E1 I want standard rate hours 'up to 8'
In F1 I want the remaining hours.
I have tried IF rules and realise these are wrong.

View Replies!   View Related
Calculating Hours Worked With Overtime Clause
I need to worked out Hours worked in a timesheet. This was the easy part, the hard part is the clause tha HR threw in, which is:

If you have worked and 8 hour day WITH 1 hr lunch then you qualify for overtime.
if you work a 8 hr day and work through your lunch (1 hour) (so equivelant to 9 hrs) you still do NOT qualify for overtime there for Overtime = 0.

This is cause some people work though their lunch to get overtime, but legally they have to have a break so we are not paying overtime for it. I have basically tried in a formula to replicate this but it works with some data and not with all.
attached is an example, as you'll see the formula works in some cells, but not others.

View Replies!   View Related
Formula To Take The Total Manhours Over 40 And Enter Them Into The Overtime Cell
The formula =SUM(E5:E39)-SUM(E45+F41) is what I'm using to enter my total man hours for the week (E5:E39) and subtracting my overtime(E45) and vacation hours.(F41). I have to enter OT and vacation on the discription cells to get the numbers to enter into the proper cells. I would like to use a formula to take the total manhours over 40 and enter them into the overtime cell.

View Replies!   View Related
Make A Calculation(addition) And Use The Answer To Multiply Against Another Addition Calculation
make a calculation(addition) and use the answer to multiply against another addition calculation....

The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.

View Replies!   View Related
IF Calculation
Hoping someone might be able to kindly help me out with this one. It's for a spreadsheet of call charges (credit crunch thing).

On the sheet, I have the call charge up to an hour ($H$5). Over an hour, it's charged per minute at the rate in $H$6.

In cell E27 I can enter the number of minutes for the call.

So basically, if E27 is up to a value of 60 then the cost is just H5, if the value in E27 is 61 or more then it's H5 + (E27-60)*H6.

I'm thinking it's an 'IF' but keep making a mess of it...

View Replies!   View Related
Calculation On Time
I have been burning brain cells trying to figure this out.
I get these numbers from an online source and they come in like this:


The times do not come in as times...when I format the cell to time it doesnt change...that is my first problem.

What I would need to do to these times is: take B and C and find what time is in the middle of them and put that in a different column.

This mess will also need to be plotted on a chart with time by the minute for one day as the X axis. In my example I drew lines on the chart to show what I mean....the blue lines I dont want charted...I use those to find the time in the middle.

View Replies!   View Related
Linest Calculation
I'm trying to implement the linest formula in a programming language for my coursework.
I've looked on excel help but it only explains on how the function selects data.

how to the values are calculated and the steps?

View Replies!   View Related
Date Calculation
I have a start date, generated onto cell B4 from a user form datepicker control. I also have a course type in cell C4 that course has a constant number of days. I would like to add the number of days of the course to the start date to give me an end date in another column.

View Replies!   View Related
Auto Calculation
i have a workbook with about ten sheets. These ten sheets have an estimated 500+ formulas each - the feed (calculate) from data on two data sheets. I now have a total of twelve related sheets that work together. I also have one additional sheet for various work named MiscWork - this sheet is NOT affiliated with the other twelve sheets.

my issue is whenever data is added, calculated, or even moved, excel recalculates ALL formulas; even on the unaffiliated twelve sheets. how do i force excel to only calculate the formulas and related data that has changed?

View Replies!   View Related
Time Calculation ...
Having been looking round this site for quite some time now and always finding what I needed I am now a registered member who needs your expertise.

I have a spreadsheet for which I need to calculate hours worked depending on a few criteria.

[data] ...

The criteria is that Sat/Eve is 8pm to 6am weekdays and midnight to midnight on a saturday. Sun is midnight to midnight on a sunday, BH is a bank holiday and basic is everthing else. What I want to know is it these columns can be populated automatically using formulas.

I would really appreciate it if someone out there is up to completing this challange, as I have to manually populate this at the moment and it can be 5000+ lines long (it takes hours). If i need to change the layout it's not a problem, whatever it takes to automate it has got to be worth the effort.

View Replies!   View Related
Availability Calculation
Has anyone got a spreadsheet that will calculate the availability of a server based on its hrs of service. Currently my spreadsheet will give me the availability of a server who's service hours are 24/7 however I have servers that are only supported 5 days a week between 07:30 and 18:30 and so only want to calculate failures that occur during that time when it comes to Availability.

I would of thought there would be a program on the market that does this but I haven't come across it and our company are kean on ITIL.

So to summarise, I key in downtimes every day what ever time of day they are but I want calculations on availability to only take in to account failures between 07:30 and 18:30 on certain servers.

View Replies!   View Related
Slow Calculation
I got a work sheet with 672 columns of information that im trying to cross compare against. I wanna compare each column against every other column in that row. I have 200 rows of data. That means each i need excel to do 226,128 comparion calculations each row. So that means in the entire work sheet its gotta do 91 million comparisons. Im on a dual core 1.8ghz core 2 duo cpu and 2 gigs of ram on xp pro with excel 2007. I even bumped up my virtual memory by 3 times the size it was yet still its taking forever.

Its taking over 3 hours to do this whole page of calculations. So i opened up visual c++ and quickly programmed in the same code with some generic values and within 3 seconds it computed it all. My guess is that the bottle neck is when excel has retrieve data from the cells because other than that i cant figure out why its so slow. Heres a section of my

View Replies!   View Related
No Of Months Calculation
in calculating the no of months in below scenario.

Fiscal year 2009 comprise of Jan 2009 to Dec 2009
Fiscal year 2010 comprise of Jan 2010 to Dec 2010
Fiscal year 2011 comprise of Jan 2011 to Dec 2011.

For example I have a period starting from Sep 2009 to Feb 2011

I need formula for the calculation of number of months in each fiscal year.

In above example number of months in Fiscal year 2009 will be 4, in 2010 it should be 12 and in 2011 it should be 2 months.

Structure of file

Starting period is mentioned in column A and Ending period is mentioned in column B. Fiscal year 2009 in column C, 2010 in Column D and 2011 in column F.

View Replies!   View Related
Same Time Calculation
I am trying to get a total column that will give the total only when two particular devices are down at the same time. This total will be taken from a long list of downtime entries for different devices but I only want the total when two particular devices are down, for example

Devicedatedowntimedateuptimetotal time
total 1/200:07:00

In the example I am just wanting to work out the total time when both device 1 and 2 were down at the same time, above the total would be 7 minutes because for 4 minutes on the 2/1/9 and 3 minutes on the 4/1/9 they were down at the same time.

View Replies!   View Related
Calculation Between Two Dates
I need to find out the amount of time between two dates for filling out
funeral benefits. The form asks how long the person has been alive in Years
Months and Days.

I would like to know if I put for instance 10/21/1955 in say A1 as the birth
date and 01/25/2006 in B1 as the date of death. So what is the formula, if
one, to calculate the time in years months and days that has passed between
the two dates?

View Replies!   View Related
Calculation According To Date
I have a data in excel , sample sheet attached.

and i have another place for compile where all the data is summarized

What i want is

If the agent name is example 1 and his mistake is present in raw data and it matches the agent id , date and financial then i want excel to calculate how many " financial " error agent made on that particular date only so that i can assign to another agents too , to get exact data no matter in whichever series that data is inserted in excel.

if i use countif and if all the condition are met it shows me all financial mistakes count and if it shows false it turns to zero . if agent make " financial mistake " on 1st nov and he made another non financial mistake then as it should show only the count of that particular agent " financial mistake " on that date only from the given RANGE DATA

View Replies!   View Related
Use IF And ROUND In Same Calculation
I'm creating a spreadsheet to calculate materials with the following columns Cost/10% of Cost/Customer Cost/Qty/Total cost.

I understand that whilst showing rounded to 2 decimal places excel stores more than this in the cell. which then throws out the Total cost by a few pence.

My research leads me to believe I need to use the ROUND function but I'm unsure which cell to use it or how.

View Replies!   View Related
Activate The Calculation?
I have a spreadsheet with several formulas where I have to go into each one of them to activate the calculation. I use F2 and enter. Automatic calculation is on. Do any of you know how this can be done automatically. A VBA-code will fit the purpose.

View Replies!   View Related
Calculation Up To A Set Value
I have a series of monthly revenues and want to calculate each month a commission % - but only want this commission calculated up to a defined limit from the previous months and current month and then to stop when the limit is reached.

View Replies!   View Related
Interest Calculation
I have a macro that formats a spreadsheet to show outstanding invoices, grouped and subtotalled by month. To add to this I need VBA code that will use the subtotals to calculate interest on overdue accounts.

Interest becomes due a calender month after the month in which the invoice is dated. So for example a January invoice would start to accrue interest on 1st March.

Below is the subtotals code (sadly the totals don't adjust if data is added or removed but perhaps that is another question for another day.)

Dim LastRow As Long
Dim NextMonth As String
Dim R As Long
Dim Rng As Range
Dim SubAmount As Currency
Dim ThisMonth As String
Dim TotalAmount As Currency
Dim Wks As Worksheet

Set Wks = Worksheets("Reconciliation")
LastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Wks.Range(Cells(2, "A"), Cells(LastRow, "D"))

View Replies!   View Related
Avoiding Re-calculation F9
I have a sheet that requires me to press F9 each time I open it to re-calculate all cells. Why do I need to do this on this 1 sheet? A few months back it was fine and didn't require the extra attention.

View Replies!   View Related
Year Calculation...
Is there an in-built function within Excel that will help me ascertain what year is next year, and what year is the year before current? I am using =YEAR(TODAY()) to ascertain what year we are currently in, but cannot figure out how to go one backwards and 1 forwards?

View Replies!   View Related
Hh:mm:ss Time Calculation
i need to total a range of cells, however, these contain time values; hh:mm:ss. it shows me the total when all cells are highlighted. but =sum() doesn't work.

View Replies!   View Related
Single Calculation
Is there a way for me to have a formula perform its calculation one time only... meaning that if the precedent data changes it (the formula) won't compute again, thus leaving the previous number it calculated unchange...

View Replies!   View Related
Calculation Direction....?
My understanding of Excel is that the calculations are performed in Column A first and then down through the rows in Column A. After all the calculations are finished in A, the calculation moves over to Column B and down the rows in B. Is this true?

I know that Excel is a little more complicated than that especially when it creates a queue for calculations, etc. However, assuming there's no calculation list/queue, would the above be correct?

View Replies!   View Related
IF/THEN Statement With Calculation
I would like to perform what seems like a simple calculation: =IF C1>0, THEN A1-C1, IF C1=0, THEN DO NOTHING. How would this look as a formula?

View Replies!   View Related
YTD Calculation
How do I calculate YTD from 1 MTD cell? The YTD cell needs to keep a running total of the MTD cell. If the current YTD cell has the number 11 in it and I typed 2 in the MTD cell the YTD cell needs to increase by 2.

View Replies!   View Related
Offset Calculation
I want to change this calculation located in AL13 -

= SUM(( OFFSET(INPUT!$A$1,12+AL6,2)+OFFSET(INPUT!$A$1,13+AL6,2))/AL5)

to say -

If (Offset 0 rows and 3 columns from AL13 = "Gross" then =SUM((OFFSET(INPUT!$A$1,12+AL6,2)+OFFSET(INPUT!$A$1,13+AL6,2))/AL5) otherwise 0)

View Replies!   View Related
Time Calculation
I have a time card sheet that I want to make automatically calculate the time I have worked. It is set up with four columns that can't be changed. The first column is "Time In(TI)", second column "Time Out(TO)", Third column "Time Taken For Lunch(TTFL)", and Finally "Hours Worked". I have no problem getting the formula to calculate the difference in "Time In" and "Time Out" but taking the 30 minutes out of that is causing a rift. If I put the 30 minutes in 00:30:00 format it will take it but it thinks it's 12:30:00 and it displays in 00:30:00 which I want it to display 30. Just to clarify, I am using =(TI-TO)-TTFL in the Hours Worked cell.

View Replies!   View Related
If Iserror Calculation
This calculation sits quite happily in a workbook doing what it is supposed to do.

=IF(ISERROR(1- OFFSET(INPUT70!$A$1,20+B3,2)/OFFSET(INPUT70!$A$1,19+B3,2)-1),0,1-OFFSET(INPUT70!$A$1,20+B3,2)/OFFSET(INPUT70!$A$1,19+B3,2)-1)

In a sister worksheet is this calculation


The name of the worksheet is all that is different.

View Replies!   View Related
Copyright 2005-08, All rights reserved