Free Microsoft Excel 2013 Quick Reference

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

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

- Need formula for calculating hours in timesheet
- How to calculate standard hours, and overtime premium seperately
- Calculating straight time and overtime
- Formula to calculate the hours worked and gross amount earned
- Timesheet problem:distinguishes between Normal Time and Overtime,
- Weekly Timesheet help
- Excel formula to calculate the average flow per hour
- FORMULA FOR CALCULATING MIN MAX INVENTORY LEVELS
- Help with formula for creating weekly hours worked
- Formula for Calculating (Accrual) Off Time (PTO)
- Need help w/formula for calculating overtime hours
- Need help w/formula for calculating overtime hours
- Timesheet Formula for Hours Worked
- Overtime for 8 hour and 40 hour
- Formula for Calculating Vacation Time Based on Anniversary AND Hours Worked
- Need updated formula for calculating time/overtime
- Calculate Hours and overtime by week
- Calculate weekday hours and weekend hours between two dates with start time and end
- Formula for Both Base and OT Wages
- Calculate Hours and overtime by week
- Calculating Overtime Hours and Pay with two Payrates
- Formula for Calculating List Price using Discount Percentage and Net Price
- Create a timesheet to add daily and weekly hours and overtime
- Which formula is correct for calculating times?

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

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

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

What I need is how to do this for the yellow cells in the attached sample.

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.

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

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

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!

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

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

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?

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!

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!

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

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.

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!

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

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

the overtime hours

the overtime hours

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