Free Microsoft Excel 2013 Quick Reference

- Tricky hour calculation (re overtime spreadsheet).
- Basic Overtime/Timesheet Formula
- Calculate overtime hours when Paying Semi-Monthly
- To display number of hours and minutes and use it for calculation purpose.
- Overtime with different rate of pay
- Auto overtime pay. There must be something better than an IF formula
- Overtime templates
- How to Calculate Time In Excel
- Turning a set amount of hours into days
- Getting rid of #VALUE!
- How to find number of hours past a spesific time
- Need help calculating overtime to date
- Executing Part Of Macro Only On Cells Meeting A Condition
- Overtime
- Calculate a trend
- Need help w/formula for calculating overtime hours
- (SOLVED) Help with the IF function to calculate my ordinary hours for a timesheet
- Need updated formula for calculating time/overtime
- Calculating Regular Overtime
- Calculate Timesheet Overtime w/Account Numbers

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):

=IF(D2<>"",SUM(D2-TIME(7,30,0)),"")

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!

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,

Dom

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.

RM

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.

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

rate.

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.

Thanks

GG

I am trying to produce and overtime sheet which covers an entire month

period.

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

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.

Regards,

SUN

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

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?

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.

=IF(A5>=40,SUM(A5-40),"0")

Thanks in advance!

Matt W

For q = 8 To Cells(Rows.Count, "t").End(xlUp).Row If VarType(Cells(q, "Q")) = 5 Then Cells(q, "t") = Cells(q, "t") + Cells(q, "Q") End If Next qI'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

CVinje

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.

Chantal

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!

natasha9794

I have attached my file!

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!

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 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.