Free Microsoft Excel 2013 Quick Reference

Calculation of overtime Results

I realize that if I just put the hours worked myself in the
spreadsheet I just created this week, i.e., did the calculation myself
and just typed in the actual hours worked, that that would avoid the
problem I'm running into. However, it would be just so much easier
and so neat to type in the actual start and end times involved and to
then let Excel figure out what the overtime is if there is indeed
overtime <g>. That would be too kewl for words! <g>

I did up an overtime logger/calculation spreadsheet. It worked fine
as is until today; today I didn't work a full day so the calculations
were useless for this date.

I yielded results on a massive search today on the net. The formula
below is what I was able to come up with and I have this in cell E2
which subtracts 7.5 hours from D2 (corresponding to regular hours
worked so must not be included in overtime calculation):


D2 shows 7h30m (calculated by end time C2 - start time B2 less 30
minutes for lunch) and E2 shows 0h0m because the above formula
subtracts 7 hours, 30 minutes to account for regular work hours vs
overtime, as mentioned above.

Up till here, so far so good.

If I do actually work 7.5 hours, then, I get this 0h0m display.
But if I didn't work 7.5 hours, like today, the above doesn't work.

I had an appointment today and so I actually only worked 4.5 hours.
D5, then, shows 4h33m instead of 7h30m.
But E5 shows #### where it should show -2h57m
(because I didn't work nearly 3 hours due to my appointment).

I've tried displaying the end results differently through cell
formatting, but nothing works.

The goal is to just put the time started in the morning and then my
usual time out and then to put the time I left. So by entering 3
different times only, and without taking out a calculator, when I left
at 6:30 p.m. tonight, the spreadsheet should determine that on this
day I didn't work overtime and that I am still actually 1 hour short
or so.

I know this is tough to understand by description, but hope the
concept itself is understood even if not the above.

So, here is the problem again in a nutshell:

How to get the above #### to display the time correctly when there is
a negative number of hours to show, i.e., in this case -2h57m
yet keep 0h0m when I did work the full day.

(I use custom time display of: h"h"m"m" to get our French Canadian
24 hours display which is easiest for all to understand as that's what
we use in our dept.)

Thanks in advance!

Hi all,
I have a spreadsheet with columns B (Date), C (Time In, 24hrs), D (Time Out, 24hrs), E (Over/Under Time) and a cell that displays the total amount of overtime.

Does anyone know of a formula that will calculate over/undertime (over/under the standard 8hr day) and another that will sum column E to give a total?

From reading around i've managed to be able to display the overtime (both in time format and decimal format) but from what i gather excel doesn't handle negative time (ie: undertime), hence why you have to use decimals instead. It's the negative time that i get stuck with as if i've left work 5minutes early, the decimal displays -0.45, yet 20minutes over is displayed as 0.20 (which doesn't seem to make sense to me).

The other problem i have is that sometimes i can work a until the early hours (8am until 2am the next morning) and this obviously causes errors.

Any help will be greatly appreciated,


Please refer to attached excel sheet.

Column B : Date
Column C : Name of Employee
Column D : Clock In Time
Column E : Clock OUT Time
Column F : Total Hrs worked
Column G : Day of the week

Week starts from Sunday and ends on Saturday

Employees are paid Semi-Monthly ( 1 thru 15th and 16th thru End of month ) which makes 2 paychecks per month.

To comply with the labor law, i want to make sure that over 40 hours per week is adjusted in ALL employees paycheck as overtime.
Bare in mind that there will be some overlap hours from previous paycheck which needs to be accounted for.

I need the best solution to this problem..
Please refer to sheet 2 where i have manually started working on but confused.

I have listed Steps of evalauting overtime below:

Each employer defines the start and stop point for its workweeks as long as it comprises a 168-hour period. For example, a workweek might run from Monday morning to Sunday night at one company while another company has the workweek run from Sunday morning to Saturday night. Federal law does not permit you to average hours worked each week together for the purposes of calculating overtime, so if your company issues paychecks semimonthly, you have to calculate the overtime due each week of the pay period for each worker.

Step 1

Divide the payroll period into the workweeks as defined by your company. For example, if the semimonthly pay period begins on a Thursday and ends on a Thursday and your company defines the workweek as going from Monday morning to Sunday night, you have parts of three workweeks included in the pay period.

Step 2

Add the hours worked for the entire workweek for the first workweek of the pay period. In this example, even though the pay period started on a Thursday, you would include the hours worked from the prior Monday through the first Sunday to figure the overtime hours. For example, if the employee worked 30 hours the Monday through Wednesday before the pay period began and 20 hours from Thursday through Sunday, the employee would have 10 hours of overtime out of the 20 hours attributed to the current pay period.

Step 3

Add the number of hours worked the second workweek in the pay period, and subtract 40 from the result to find the number of overtime hours worked. The second week will always be a full week with semimonthly pay periods. For example, if the employee worked 45 hours, 40 hours would be at regular pay and 5 hours would be at overtime pay.

Step 4

Calculate the number of hours worked in the final week or partial week of the semimonthly pay period. If the employee has not yet exceeded 40 hours, do not pay overtime for the current pay period. However, include these hours when figuring the overtime payments for the next pay period. In this example, since the workweek ends on Thursday, if the employee has worked 40 hours so far that week, you would not pay any overtime, but if the employee worked any more hours that Friday through Sunday, those hours would be included as overtime on the next paycheck.

Step 5

Add the overtime, if any, from each of the weeks in the semimonthly pay period. In this example, add the 10 hours from the first week to the 5 hours from the second week to get 15 total hours of overtime.

Let me know if you have any questions.

Thanks, help is always aprreciated.



I have a column to state the number of hours of overtime and to multiply it with the hourly rate in another. My question are
1) how to show the column express in hours and minutes like 1.30hrs or 1 hour 30 min?
2) can it be used for calculation purpose?

Thank you so much for your advise.

hi im a beginner in excel and i need an overtime sheet which calculates the hours of

employees with standard and over time rate. But the hardest part for me is that

there are so many conditions which is confusing me.

1)Week start from Sunday to Saturday
2)standard hours are 40/week
3) if it goes above 40 they are entitled for an overtime
4)now there are different rates of hours.
5)std day rate, std night rate, std day overtime rate, std night overtime rate,

weekend day rate, weekend night rate, weekend day o/t rate and weekend o/t night


The hours can start from any day and finish by saturday.
would like to calculate if
1) they work on days, they get paid for days.
2) if they work for nights, then get paid for nights.
3) if the work in the middle they should get paid both night and day mixture
4)as soon the hours hit above 40 , the o/t starts.
5)only those hours need to be calculated for overtime which are after 40 hours. Now

the hard part is , they can be days, nights , or weekends.
I am so confused as I dont know how to get it right.

Any help will be appreciated.

Please look through, help is desperately needed and greatly appreciated.

I have an excel file that I use to calculate staff over time. I'm hopoing to automate a lot of the process by writing a formula, but need some serious help.

I have 6 staff all with different hourly rates. When I enter their name in a cell I would like excel to auotmatically calculate their overtime.
I have a column of the 6 staff and next to it a column with their hourly rates, this is on a sheet called "wages".
On another sheet, called "overtime" i would like to enter the name of the person doing the overtime, in the next column I would enter the amount of hours, the third column should then return their overtime pay. (eg; John, 8, (john's rate*8*150%)

I have attempted to use the IF formula,

=IF((overtime!b3="John"),B1,IF((overtime!B3="Bill"),B2,IF((overtime!B3="Bob"),B3,IF((overtime!B3="Di ck"),B4,IF((overtime!B3="Helen"),B5,IF((overtime!B3="Mary"),B6,"entername"))))))

The problem is that this only watches one cell, overtime!B3 for the name of the staff member and i have to replicate this formula for every cell that needs watching.

I was hoping I could enter the staff name into any cell.
Is this possible?
Is there a better way?

Please help. I would greatly appreciate all and any advice.

Can anyone help?
I am trying to produce and overtime sheet which covers an entire month
The columns consist of date, day of week, start and finish times and total
hours worked. There is also a cell that contains the daily contracted hours
which is used to help calculate the overtime hours worked (if any) for that
day. The calculations works fine for the week days but on the Saturday and
Sunday (non working days) it treats the day as a normal working day and
produces overtiem hours for a Saturday as if it was a normal week day.
Does anybody know what formula can be entered in the cells that will sum up
the total hours worked for any day, but in addition differentiate between a
normal week day and a Saturday and Sunday, then return the full overtime
hours for that day.

Hope you can help

Dear All,

how can we calculate time spent by an employee, in case the company gives the double payment for the over-time. the working hours are "8 hours" per day and more than 8 hours means overtime-hours". how cud we calculate on monthly basis supposing an employee makes some leaves as well in excel. try to extend your supposition to escape each and every doubt for over-time hours-calculation of an employee in excel.

Please do the needful.


Bad thread title but I really couldn't think of a better one/how to explain it!

Basically, I am trying to create a spreadsheet that records the shifts that people work and to calculate sickness, overtime etc. I am recording each shift in hours (7.5 per shift). However, the person asking for the spreadsheet needs annual leave recorded in days.

How can I make excel know that 7.5 hours = 1 day?


I'm using excel 2000

I have a spreadsheet for calculating Overtime

A = Hourly Rate
B = Single Time Hours
C = Single Time Cost
D = Time and a Half Hours
E = Time and a Half Cost
F = Double Time Hours
G = Double Time Costs
H = Total costs

In columns C,E and G I have the following formulae

C > =IF(B3="","",SUM(B3*A3))
E > =IF(D3="","",SUM(A3*D3)+(D3/2*A3))
G > =IF(F3="","",SUM(F3*2*A3))

which calculate the cost of overtime, but leave the cell blank if no hours are enterred in B,D & F

The problem I have is that unless I enter an amount in all three hours columns I get #VALUE! in column H where the formula is =C3+E3+G3

How can I get rid of the #VALUE!, but keep columns C,E & G showing as blank

Thanks in advance for any help

Im pretty new to excel.
At least with other things than the regular - + * and /.

I have a huge problem getting the hours past 12:00 o'clock when I have a field with start and stop time. For instance 8:00 (A1) to 16:00 (A2). I should get 4 hours. As to the 16:00, I use 24 hour format.

I have tried with plenty of IF statements but to little help. It just don't seem to work with hours. Only with numbers, text and so on. Maby it's the : it's not cooperating with. I'm not sure.

I want to calculate my overtime. Past 12:00 is 50% on saturdays.

What are my options?

So here's the deal... I need to calculate the total number of overtime hours to date. I've searched the threads but have come up short with what I need to accomplish.

My spreadsheet has a TOTAL OVERTIME cell (A5). Cells B5 through BA5 represent the 52 weeks in a year. Each time a value is entered in any of those cells, I would like the TOTAL OVERTIME value to update (overtime is anything over 40 hours). So if 41 is entered in B5, the total overtime to date would be 1. If 44 is entered in C5, the total overtime to date would be 5 hours (1 hour from the previous week and 4 hours from this week).

This is the formula I found, but this only does one 40 hour period. I need something a little more complex.


Thanks in advance!

Matt W

I'm working on a spreadsheet designed to track total overtime hours worked in a year; on the spreadsheeet is a column to keep track of each day's total OT, the week's total OT, as well as a column to track the year's total. There is a formula in the week total, but the year total is calculated via a macro (day of the week total, added to the existing year total, result updated, so, each day has it's own button and macro). We have a shift that works a different week than the shift that needs to track overtime, but still must be included in the list. Therefore, I created a column to place the shift designator so there can be recognizable diffrerentiation. With quite a bit of help from this board, and others, I've created (or been kindly given) the following macro (this is just a part of it) to total the day's overtime and existing year overtime and input the result into the cell. I now need to have this executed only when the condition I specify (say, in cell D1) is met (that would be the shift, for example the text M1 or SST). Please note, the week totals are only for user reference - they do not come into play for calculations of year totals. The below macro actually takes the totals from a day of the week and adds it to the existing year total, placing the result in the year total column.

  For q = 8 To Cells(Rows.Count,
    If VarType(Cells(q, "Q")) = 5 Then
       Cells(q, "t") = Cells(q, "t") + Cells(q, "Q")
    End If
  Next q
I've attached a screen capture to illustrate my question. You'll notice two additional columns for "Force" overtime. These will have to have the same macro as above executed on them to calculate and track a running total for the year versus the week's total (appropriate changes to cell designations already made & working). I should be able to modify any code to work with both, I'm just having a problem getting the macro to work properly on either based on my condition. Your help, is again and always, appreciated.

Using Excel 2003
Windows XP


Ok. I am attempting to make a spreadsheet that will automatically estimate what my paycheck will be each week. The only reason why this is not completed yet is because the some of the rules of the company I work for are really weird and make the formulas a bit complicated at times. Where I'm running into problems currently is overtime. Right now we are on 4 , 10 hour shifts, however if we work 11 hours one day that does not automatically make it overtime. We have to work more than 40 hours a week to get overtime. so for example If i work:

Monday: 9.4 hours
Tuesday: 10.2 hours
Wednesday: 10.9 hours
Thursday: 11 hours

I would like to calculate how much overtime I will receive for each day. For this example I will not receive any overtime until Wednesday, because I need to make up for the lost time on Monday. So I should receive .5 hours on Wednesday and 1 hour on Thursday for a total of 1.5 hours of overtime.

What may help is I also have a column called short work week... This is because if I do indeed work only 9.4 hours I get paid .6 hours of short work week pay (aprox 80% of our wage. This company guarantees us a 10 hour day so when we don't work it they have to pay us that) This may be helpful in calculating this.

I've attached what I have so far. I'm going to continue to work on some of the other parts of it (maybe it will give me some inspiration on the overtime part. I've been staring at this for way too long lol) Any suggestions would be appreciated


I need to make an estimation on the percentage of overtime paid vs. the base salary.

I have a table with monthly figures vor 2006 and 2007 :
- the base salaries
- the overtime salaries

I've calculated the percentage, and there is a downward trend.

What formula do I use to make an estimation of this percentage for the period 2008-2012 ?

I've read the info on trend, forecast, etc. but dont quite understand it.

Many thanks in advance.


i am trying to create a timesheet that calculates cells automatically when
data is entered. I am entering time "in" and "out" two times (first half of
day total, then second half of day total, after meal break) and then i have a
formula which calculates those totals. In algebraic terms, say: a+b=c, then
for the second half of the day: d+e=f. then c+f=h, to give total hours
worked. what i need to have is a formula that calculates a new cell (say,
"i") so that IF h is greater than or equal to a value of 8.0 (hours per day),
then it automatically calculates the amount of hours over 8.0, keeping the
regular hours worked 8.0 in cell "h", and calculating the overtime hours as
anything over 8.0 into new cell, "i". hope that makes sense? can anyone
help me? thank you!!!

Im am having trouble with the IF function in excel 2010, for a template of a pay sheet.
I have been asked to -Using an IF statement in cell G8, calculate the ordinary hours component of total hours.
Using a nested IF statement in H8, calculate the overtime hours.
Using a nested IF statement in I8, calculate the time and a half (T ½) overtime hours
Using a nested IF statement in J8, calculate the double time hours.
The ordinary hours are 8hrs a day.
On weekdays, the first 2 overtime hours are paid at time and a half and remaining overtime hours are paid at double time rates.
Hours worked on Saturday are considered overtime, with the first 2 hours worked at time and a half rates and remaining overtime hours are paid at double time rates.

As you can see in my file i have just put the normal hours which is 7.30am - 4.00pm,with a 30 min lunch break, so i am not sure how to work out the IF functions.
I will be putting the employees actual hrs in my next spreadsheet, this will be the template i will use to calculate the info.

Can anyone please help me with this function,
I would greatly appreciate it!

I have attached my file!

Hello, a while back I posted this timesheet (GA Semimonthly Timesheet Template.xls) to get a formula to calculate overtime (any time worked over 8 hours in a day). It has been working great.

Now I would like to add two new columns to make space to include a second daily break, and I don't know how to update the formula to include the new columns. Can someone help me with this? Here is the new version of the spreadsheet:
GA Semimonthly Timesheet Template v2.xls

Thanks in advance!

I'm having an issue with the attached time sheet.

The formulas work under ever scenario accept if an employee works "overtime" hours, hours worked over 40, and has hours under the “Other” column (I13), hours like when the building is closed, it adds wrong under the “Regular overtime” column (D15). The formula we have in there is =IF(J$3="salaried","N/A",IF(ISNUMBER(C13),MAX(MIN(C13,40+I13)-36+I13,0),"")). We want D15 to calculate any hours worked over 36 hours that is not over 40 hours plus “other” hours. I’ve spent a ridiculous amount of time on this and am still scratching my head bc any changes I make messes the time sheet up in just a regular overtime scenario with no overtime.

I think the issue is with cell D15's formula calculating the "Other" column too much when there are overtime hours.
I need it to calculate:
- if C13 is greater than 36 than C13 - 36 + I13
- but if C13 is greater than 40 than D15 would calculate the difference between 36 and 40 +I13.

Attached the correct time sheet should refect:
- Hours Worked (C15) = 36
- Regular Overtime (D15) = 9
- Overtime (E15) = 10

Please help if you can! Thank you!

I have researched for a long time and have not found my answer, so I am going to try to explain it here since I always seem to get good results!
I have a timesheet that I am trying to calculate overtime on...but it is more complicated than the examples I have found on the suggested sites.
Our techs are required to document each type of work per day and code it to the appropriate account code. In one given week, they can have 50+ entries on their time sheet. So here is how I have it set up:
Col D***********Account Code
Col E***********Start Time
Col F***********Stop Time
Col H***********Hours (This will calculate the time worked on this entry)
Col I***********Accum Hours (Calculates running total hours for the week)
Col Q**********Lists the Account Codes
Col S**********Compares Col D (Codes entered) with Col Q (Codes List) and lists the hours for each account code.
It works perfectly and puts the amount of hours for each code for the entire week into Column S. However, I need to find a way to show overtime in another column....probably Col T. So once the Accum Total in Col I reaches 40, the hours need to go into Col T (Weekly Overtime Hours) and all hours under 40 hour would go into Col S (weekly total hours) I wanted to ask for help on this a long time ago, but hoped I could figure it out, but I can't seem to get it. Plus I didn't know if I could really explain what I am trying to accomplish.
If anyone can help me I would so appreciate it!!!! If I need to explain something better, please let me know.
THANK YOU in advance!

No luck finding an answer? You could always try Google.