Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Timesheet formula for calculating ordinary hours and overtime hours.

Hi I am building a timesheet and I have become a bit stuck.

I have already got the timesheet to automatically calculate the number of hours once the start time and end time are entered(these cells are all in hh:mm format). The column which calculates the total time is AA4.

Then I have the following columns:
Wages rate: Y4
Ordinary total: AB4
Overtime total:AD4
(See attachment)

After 38 hours we need to pay at the overtime rate, so I can't just multiply the total hours by their hourly rate.

I need two formulas that will

1. Work out ordinary hours: If the number of hours is less than or equal to 38 it will calculate the hours (up to 38) by the rate, but if it is greater than 38 it will just multiply the rate by 38 (giving maximum number of ordinary hours)

2. Work out overtime hours: Where the hours exceed 38, I need a formula that will calculate how many hours are greater than 38 and multiple these additional hours by the rate by time and a half.

Can someone help me I have been stuck on this for two weeks!

Cheers


Post your answer or comment

comments powered by Disqus
I am putting together a time sheet, and I am trying to figure out a formula for calculating overtime hours. Anything over 8 hours is considered overtime, and I would like those hours to appear in a different cell.

In my example (attached), you can see that the hours for 1/20 are over 8 hours, and 10 hours is showing in the "Total Regular Hours" column. I would like for all hours entered in this column to stop at 8, and any hours over 8 would show in the "Total Overtime Hours" column. (In this case, there should be an '8' in the "Total Regular Hours" column and a '2' in the "Total Overtime Hours" column.)

I've tried a few different options, but I can't figure out what the formula would be to achieve this result. Any help would be greatly appreciated!Timesheet Worksheet.xls

Hi,

I need to find a formula that can calculate and seperate overtime premium from total hours worked.

If someone works 49 hours, and is contracted for 39 hours, I need the hours to be calculated as below

Total hours 49 49
Overtime premium at 1/3 of time 4 (restricted to 4 hours) 4
Overtime premium at 1/2 of time 4 (restricted to 4 hours) 4
Overtime premium at double time 2 (no restriction 2

I have attached a sample spreadsheet.

Many thanks

Sean

I'm pretty new at the whole Excel thing and many of the formulas are way over my head.
I'm trying to figure out how to make a time sheet calculate straight time and overtime and I can not figure it out. The time sheet from the Excel Bible was close, but not quite what I'm looking for.

I work seven rotating twelve hour shifts and anything over eight hours and anything over twelve hours is overtime. The shifts start at 0600 and 1800.

I have several questions.
1. how do I get the times to calculate? It works correctly when doing one shift (days), but not the other (night). It adds up to 24 on night shift.
2. how do I make it seperate the twelve hours into 8 straight time hours and four over time hours?
3. how do I get it to know when it has reached the 40 hour mark?

Hi,

I would like some help with a formula.

I have a spreadsheet which has columns with date ranges and an amount of hours worked during that date range. I then have other columns with different date ranges. These date ranges cross over with the date ranges of the first ones. I need to find out how much is earned in the second lot of date ranges from the other information.

It's hard to explain, but if you take a look at the attachment you will understand. Under the first lot of columnns you will see that there are week and fortnightly date ranges. Then total amount of hours worked in that period. I then have a column which works out the amount of working days during that period. I then have hours worked per day, hourly rate and a gross amount for that period. I then have a different set of date ranges. I need the formula to calculate the hours worked and gross amount earned in the second lot of date ranges based on the info from the first set of date ranges. For example: fortnight 22/1/12 - 4/2/12 the person worked 53 hours at $25 per hour.This equals a gross amount earned of $1325. And for the fortnight 5/2/12 - 18/2/12 the person worked 76 hours at $25 per hour. This equals a gross amount earned of $1900. Now, the other fortnight date range shows fortnight 24/1/12 - 6/2/12. Based on the info from the first columns I need to work out what the hours and gross earnings are for the period 24/1/12 - 6/2/12.

Thankyou for all your help

I need to setup a timesheet that distinguishes between Normal Time and Overtime, with Overtime being work between the hours of 22:30 and 06:30, and all other time is Normal.
What I need is how to do this for the yellow cells in the attached sample.

Hi
Hope this makes sense.
I am new at this so if anyone can help with a simple formula for me it would
much appreciated.
I need to make up a weekly timesheet that can calculate normal hours and
overtime hours for each day and show in a seperate column.
If work 35 hours a week
A column showing hours worked at regular, meal times deducted, overtime
hours automatically calculated for the day. At end of sheet total colums for
regular hours, overtime hours, minus meals hours automatically calculated.
--
You are neither too young or old to learn.

I have attached the excel sheet with sample data. I need more generalised formula for flows per hour and flows per day since the existing formula in the sheet has many drawbacks:
1. At the end it shows a negative value and cant avoid it (since the number of rows is not fixed, I get this data from database by using a macro)
2. If the flow at any particular time( in this case say 19032012 00:36 is missing) fails then the whole formula for flow per hour goes wrong

Can I have a formula which calculates flows per hour depending on the time ( in this case 00:06 to 00:51)

Thanks in advance

DOES ANY ONY HAVE THE FORMULA FOR CALCULATING MIN MAX LEVELS FOR INVENTORY
CONTROL

I know this is probably easy to do but i've been trying for hours and looked on the internet with no luck. I'm basically trying to set up a spreadsheet which will keep track of the hours i've worked and total them up for me.

I've attached a copy of the kind of thing i'm hoping to achieve and have entered an example weeks work and manually worked out the hours each day and the total hours worked that week, but all i want to do is to enter the hours i've worked and have it work out the total for that week for me. The main problem i kept coming across was when i worked, for example, 6 hours and 30 minutes, i want this to show as 6.5 hours.

I would appreciate any kind of help on this and thank you in advance for any advice you have for me.

Thanks again

Formula for Calculating (Accrual) Off Time (PTO)
I need to develop a tracking spreadsheet (for 18 employees) that will calculate accruing vacation time (on one worksheet) based on the following parameters:

Employees who have worked less than 1 year with the company:
- 10 days
Employees who have worked at least 1 year with the company:
- 12 days
Employees who have worked at least 2 year with the company:
- 13 days
Employees who have worked at least 3 year with the company:
- 14 days
Employees who have worked at least 4 year with the company:
- 15 days
Employees who have worked at least 5 year+ with the company:
- each year will add 1 day until reach the max 20 days

In addition I need to be able to deduct vacation time used. Does anyone have any suggestions for layout or for a formula that can do part of these functions? I appreciate any advice!

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

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

Hi All,

I need help on time formula.I need the right formula to calculate the difference between time a staff started to work on an item and the time they completed the item. I also need to calculate how much time left for the balance of the day and the average time needed to complete the balance of the gtarget item. Example is the target for the day is 22 items. If the staff started to work and completed the first item at 8.15pm ( Her shift is 8pm to 5am), then how much time is left and how much is the average time needed to com plete the balance of the 21 items for the day.I also need the formula to adjust automatically with the allocated time.Example is 8 hours on normal days and 9 hours when there is one hour overtime.

I tried several methods but could not arrive at the right solution. I have attached the spreadsheet. The columns in red is where I need the formula.

Appreciate if anyone could teach me how to input the correct formula. Thanks.

Need help on how to do calculate formula when using 8 hour and 40 hour
overtime rule. What I am doing is try to build spreadsheet for tracking of
working hours on regular hour, days off with pay and overtime, below is
example of what it looks like:

A B C D E F G H I
1 Hour Type MON Tues Wed Thur Fri Sat Sun Total
2 DayOff 0 0 8 15 14 0 0 37
3 Regular 14 15 0 0 0 17 12 58
4 Overtime ? ? ? ? ? ? ? 0

On days off employee gets time off with pay, if employee works on that day
off and works over 8 hours it falls into 8 hour rule overtime for each day.
If he works on regular work days he has to work 40 hours before start getting
the overtime. I have to use both 40 and 8 hour overtime, question is how do
you use formula to work with both 40 and 8 hour in roll 4 for overtime?

Hello,

I finally figured out the formula to calculate vacation time based on anniversary date for full-time employees.

Now, within the same sheet, I need to calculate vacation time based on anniversary date AND hours worked for part-time employees.

Here is the criteria:

0-3 years of service 1 hour for each 20 hours worked with a maximum of 4 hours
3-15 years of service 1 hour for each 13 hours worked with a maximum of 6 hours
15+ years of service 1 hour for each 20 hours worked with a maximum of 8 hours

Example: A part-time employee works 50 hours per pay period and his start date was 8/25/2002. Manually calculating, he would earn 3.8 biweekly
A part-time employee works 32 hours per pay period and his start date was 6/19/2011. He would earn 1.6 biweekly

Thanks!

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 am trying to calculate employees pay with overtime starting after 40
hours per week.
Below is an example for two employees over the two week pay period:

Date In Time In Date Out Time Out Total Time
E555555555N
D1107191811080036R 11/07/05 19:18 11/08/05 0:36 5.30
D1109182411100015R 11/09/05 18:24 11/10/05 0:15 5.85
D1112122611121716R 11/12/05 12:26 11/12/05 17:16 4.83
D1114120111141711R 11/14/05 12:01 11/14/05 17:11 5.17
D1115170111160043R 11/15/05 17:01 11/16/05 0:43 7.70
D1118115311181707R 11/18/05 11:53 11/18/05 17:07 5.23
D1119121711191703R 11/19/05 12:17 11/19/05 17:03 4.77
E333333333N
D1107124111080036R 11/07/05 12:41 11/08/05 0:36 11.92
D1110065811101702R 11/10/05 6:58 11/10/05 17:02 10.07
D1111065911111958R 11/11/05 6:59 11/11/05 19:58 12.98
D1112080711121701R 11/12/05 8:07 11/12/05 17:01 8.90
D1113065811131604R 11/13/05 6:58 11/13/05 16:04 9.10
D1113160411131658R 11/13/05 16:04 11/13/05 16:58 0.90
D1114170611150045R 11/14/05 17:06 11/15/05 0:45 7.65
D1115171011160043R 11/15/05 17:10 11/16/05 0:43 7.55
D1118085911181733R 11/18/05 8:59 11/18/05 17:33 8.57
D1118195011182100R 11/18/05 19:50 11/18/05 21:00 1.17
D1119085911191609R 11/19/05 8:59 11/19/05 16:09 7.17
D1120090011201700R 11/20/05 9:00 11/20/05 17:00 8.00

The first column is the raw data that is given to. I have broken out
the data into a readable format and calculated time for each day. I
need to find total time for each of the two weeks so I can calculate
overtime (if any) then calculate total time per employee for both weeks
and overtime.
The pay first week started on 11/07/05 and ended on 11/13/05. The
second week started on 11/14/2005 and ended on 11/20/05.
I attached the above as a .doc file to make copy/paste easier.
Any suggestions?

+-------------------------------------------------------------------+
|Filename: payroll.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4060 |
+-------------------------------------------------------------------+

--
gregt812
------------------------------------------------------------------------
gregt812's Profile: http://www.excelforum.com/member.php...o&userid=17539
View this thread: http://www.excelforum.com/showthread...hreadid=487714

Need to generate a formula that will calculate seaprately, weekday overtime chargeable hours which start from 7am to 7pm(12hrs), and weekend overtime hours which is 24 hrs. For example

A1) Start date&Time = 06/oct/2011 07:00
A2) End date&Time = 09/oct/2011 10:55

Overtime hours for weekdays are only chargeable from 7am - 7pm, whereas overtime hours for weekends (both saturday and sunday) are chargeable for the whole 24hrs.

I am attempting to calculate base pay and overtime pay separately for workers that have a number of variables. The work week is 6 days long and in each day a worker can have up to 5 different hourly rates depending on where they work and up to 5 different overtime rates a day (over 40 hrs) if they have worked over 40 hours at any point in the week. The columns I have so far are:

Hours worked H4:H33
Hourly rate I4:I33
OT rate J4:J33
Sum of hours worked K4:K33
Wage L4:L33

I have tried a number of formulas to calculate the wage(s) and can’t seem to come up with a formula that allows me to calculate base pay and OT pay separately. Can anyone help? Thank you for your help!

I am trying to calculate employees pay with overtime starting after 40 hours per week.
Below is an example for two employees over the two week pay period:

Date In Time In Date Out Time Out Total Time
E555555555N
D1107191811080036R 11/07/05 19:18 11/08/05 0:36 5.30
D1109182411100015R 11/09/05 18:24 11/10/05 0:15 5.85
D1112122611121716R 11/12/05 12:26 11/12/05 17:16 4.83
D1114120111141711R 11/14/05 12:01 11/14/05 17:11 5.17
D1115170111160043R 11/15/05 17:01 11/16/05 0:43 7.70
D1118115311181707R 11/18/05 11:53 11/18/05 17:07 5.23
D1119121711191703R 11/19/05 12:17 11/19/05 17:03 4.77
E333333333N
D1107124111080036R 11/07/05 12:41 11/08/05 0:36 11.92
D1110065811101702R 11/10/05 6:58 11/10/05 17:02 10.07
D1111065911111958R 11/11/05 6:59 11/11/05 19:58 12.98
D1112080711121701R 11/12/05 8:07 11/12/05 17:01 8.90
D1113065811131604R 11/13/05 6:58 11/13/05 16:04 9.10
D1113160411131658R 11/13/05 16:04 11/13/05 16:58 0.90
D1114170611150045R 11/14/05 17:06 11/15/05 0:45 7.65
D1115171011160043R 11/15/05 17:10 11/16/05 0:43 7.55
D1118085911181733R 11/18/05 8:59 11/18/05 17:33 8.57
D1118195011182100R 11/18/05 19:50 11/18/05 21:00 1.17
D1119085911191609R 11/19/05 8:59 11/19/05 16:09 7.17
D1120090011201700R 11/20/05 9:00 11/20/05 17:00 8.00

The first column is the raw data that is given to. I have broken out the data into a readable format and calculated time for each day. I need to find total time for each of the two weeks so I can calculate overtime (if any) then calculate total time per employee for both weeks and overtime.
The pay first week started on 11/07/05 and ended on 11/13/05. The second week started on 11/14/2005 and ended on 11/20/05.
I attached the above as a .doc file to make copy/paste easier.
Any suggestions?

I'm not sure I'll explain this well, but I'll try my best:

I currently have an Excel sheet that calculates the hours considered overtime, the pay rate, and the total regular pay and OT pay just fine. The problem I'm having is that one employee performs two funtions at my company, one as a server at 2.13/hr, and one of a managerial nature at 15.00/hr. I'm at a loss as to how to modify her entry in my excel workbook to both calculate when her overtime begins and how many hours at what pay rate to display in the appropriate cells.

For all the other employees I do this by using an IF statement that determines if their hours for the week are above 40, and if so, to display 40 in the Reg. Hours cell, and the difference in the OT Hours cell. From there calculating the amount of pay to be distributed is easy. I am just having problem figuring out how to do this same thing but while dealing with two different payrates.

I'm not sure if I attached the helppayroll.xlsx file correctly, but if not I'd be happy to find a way to post a sample online or to email it to you.

I hope I made my problem clear enough.

David

Hello, I have a net price (C4) from a supplier along with a percentage discount (D4) and want to calculate the starting list price. Can you help me with a formula for this? Thank you.

i want to create a timesheet that add up daily and weekly hours, and to add
the overtime hours

I built a spreadsheet to calculate hours worked; I did it over a couple of weeks, reading and learning as I went. During the time I was doing this, somehow I ended up with 2 different formula for calculating hours worked...these are:

=IF(N31>O31,MIN(("24:00"-N31+O31)*24,7.25),MIN((O31-N31)*24,7.25))

and

=IF(N32>O32,CEILING(("24:00"-N32+O32),7.25),MIN((O32-N32)*24,7.25))

where N and O are the start & finsh times respectively.

Both seem to calculate properly, but does anyone know which is the better formula to use...of does it matter ? Thanks.


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