# Calculating Hours Worked With Overtime Clause

Jul 23, 2007
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 8 Replies
ADVERTISEMENT
Jan 13, 2014

Lunch is not paid. Holiday and vacation hours get calculated at the regular pay rate. Overtime is anything in excess of 8 hours per day and/or in excess of 40 hours per week and/or over 5 working days per week. Saturdays for most the employees will be overtime because it will be their 6th workday of the week; but it will be regular time for one employee as it will only be his 5th workday of the week.

For accounting and payroll purposes, we need the totals to display in both hour and decimal format.

So far, I have Lunch, Regular and Overtime hours figured out, but I still need to work with Saturday, Vacation and Holiday hours. Also, currently, the time in and out has to be typed in with the colon and AM or PM. Is there another way to input the info without having to type in those items? I'm trying to make it as user friendly as possible.

View 2 Replies
View Related
May 9, 2008

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. I’m using a macro and some complicated helper cells to be able to sort the employees based on their total OT hours worked.

View 13 Replies
View Related
Aug 4, 2013

My overtime pays is anything exceed over 8 hours per day or over 40 hours per week. Right now I can only calculate overtime by either over 8 hr/day or over 40 hr/ week. I need a way to combine both.

View 9 Replies
View Related
Aug 11, 2010

I have been working on a timesheet but the problem I have come across is calculating actual hours worked only in the core hours and any work outside the core hours is calculated in the outside hours column. A standard work day is 7.6 hours working between 8.30am and 5.00pm. However if someone was to commence work either before 6am or after 8pm this is outside of core hours. I have attached an example of my timesheet for you to see what I am talking about.

View 3 Replies
View Related
Feb 20, 2014

Example: In cells A1:A10 random number between 0 & say 20, need to sum ABOVE 8 = (calculating overtime hours)

E.g.

A1 = 0

A2 = 8

A3 = 8

A4 = 10 (giving 2)

A5 = 12 (giving 4)

A6 = 5

A7 = 13.5 (giving 5.5)

A8 = 8

A9 = 0

A10 = 16 (giving 8)

A11 = (Total overtime above 8 hours) 2+4+5.5+8 = 19.5

Need to be able to increase rows and drag across.

View 3 Replies
View Related
Nov 30, 2012

I thought I had everything worked out with this timesheet but I've discovered one more problem.

Weekly Timesheet.xlsx

The total overtime hours needs to show that anything over 40 hours in the Total Regular Hours cell is overtime. And it also needs to show only up to 40 hours in the Total Regular Hours cell.

View 8 Replies
View Related
Jan 1, 1970

I can do the timesheet formula for adding the hours worked as follows:

Start Finish Total

08:45 17:15 8.5

However, it doesn't work when I fill in a whole week work of hours in this format:

Start Finish Total

08:45 17:15 08:30

It works on a daily basis, but when total hours exceeds 24, the formula get's all mixed up - how to I format the total column to account for every 5 minutes worked, which you can't do when converting to decimal??

View 14 Replies
View Related
Nov 27, 2012

Weekly Timesheet.xlsx

This spreadsheet calculates hours worked great for first and second shift but when you enter times for third shift it goes all whacky with the outcome.

View 10 Replies
View Related
Jan 28, 2014

I'm trying to create a macro that will take name values from a list (Last name, First name) and then find these particular names in a long list of names in another sheet. Once all the occurrences of the name are found, the macro would need to add up all the hours that the person has worked during a given month. For example, I have Jane Doe and John Smith on my shortlist, the macro would have to take the name of the first person, Jane Doe, find all the occurrences of that name in the second sheet that contains names, dates and hours worked, then add up all the hours worked by that person and return the total hours worked value for that parson. Then take the name of the second person, John Smith, and do the same, and so on. Is it possible to do it with macros?

View 2 Replies
View Related
Aug 6, 2007

If a Rescue Officer is called out at 23:00 and is back at 04:00, this should equate to 5 hours worked.

It seems that if my times are all on one side or the other of a 24 hour cycle, my calculation work fine but it it breaks across the 24 hour (as above, it doesn't work.

A2=04:00

A1=23:00

Using (A2-A1)*24 give me -19.00 hours

My SS macro has a line:

s = (wks.Cells(c, 3) - wks.Cells(c, 2)) * 24 'calculates the duration of time worked

Is there any way of getting excel to calculate an elapsed time in hours when the start and end times roll over from one day to the next?

View 9 Replies
View Related
Mar 23, 2013

I need creating a formula that will tell me the total number of employee hours worked during a certain hour (6:00 AM) for a particular department. Some employees clock in at 6:00 AM, some clock in at 6:30. Here is the current formula I'm using which only tells me the amount of employees in the department not how many hours worked . I have three employees that clock in at 6:00AM and one at 6:30AM, the total I'm looking for is 3.5 but my formula gives me the result of 4.

C5:C1446 is a list of departments

T1503 is the 6:00 AM

D5:D1446 are the Clock In times

K5:K1446 are the Clock Out Times

=SUMPRODUCT(--($C$5:$C$1446="Shipping"),($T$1503>=$D$5:$D$1446)*($T$1503

View 6 Replies
View Related
Jun 30, 2014

I would like to calculate staff meal deductions for each employee based on the number of hours they worked for that day.

I have the hours listed all on one page, per day, per employee.

I have the staff meal deductions range listed on another page in the workbook.

The range is as follows: *Please note anyone working over 8.00 hours is deducted a maximum of $2.40 for that shift/day.

Hours

Deduction

1.00

$0.30

[Code].....

View 4 Replies
View Related
Feb 21, 2013

Is there a way to conver a persons time spent (given in weeks) to adjust/convert to show per month. Attached is the sheet. Do note that week 2/25 - 3/1 is a combination of Jan and Feb so hours should be logically divided into jan and feb...

Name 2/18 - 2/22 2/25 - 3/1 3/4 - 3/8 3/11 - 3/15 Feb mar

Tom 40 10 0 20 ?? ??

name

2/18-2/22

2/25 - 3/1

3/4 - 3/8

3/11-3/15

Feb

Mar

tom

40

10

0

20

??

??

View 3 Replies
View Related
Dec 15, 2013

I'm attempting to make a simple time sheet for a handful of employees. I'd like to enter the clock in time and clock out time for each day. The end cell should be the running total for the week. The tricky part for me is having the formula subtract an hour for each day that is over 5 hours.

View 3 Replies
View Related
Jan 13, 2009

I am making a schedule and I would like it to take out a 30 min break if the hours worked is over 6 hours.

I have so far

A B

1 11:00 7:30

=24*(B1-A1)

Gives me 8 hours, I would like it to subtract the 30 minutes only ifthe sum is over 6 and not alter the sum if it is under 6.

View 9 Replies
View Related
Mar 19, 2009

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 9 Replies
View Related
Aug 6, 2008

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 5 Replies
View Related
Apr 3, 2008

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 9 Replies
View Related
Jan 15, 2010

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 9 Replies
View Related
Jan 31, 2009

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 9 Replies
View Related
Feb 13, 2014

calculate overtime hours from daily time entries.

Normal hours are 7.6 per day

Time 1/2 is hours over 7.6 but no more than 2 hours

Double Time is all hours over that.

I have the spreadsheet with the days of the week in one row and at the end I have 1 cell for Normal Hours, Time 1/2 and Double Time. I need a formula that will work out overtime off each day and add for all days of the week and enter data into one cell. So all normal hours are in Normal hours and Time 1/2 and Double time are automatically calculated once hours are put in per day manually.

WedThurFriSatSunMonTuesTotal Normal HoursTime 1/2Double Time 10101068

View 5 Replies
View Related
Mar 20, 2009

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 8 Replies
View Related
Apr 15, 2014

I have to calculate employees work hours for overtime.

in the timesheets:

A B C D E

1 ID Date Code Hours PayMethod

2 A123 4/14/14 TRN 20:00 Regular

3 A123 4/14/14 TTT 15:00 Regular

4 A123 4/14/14 TRN 13:00 <----- total for cell D2,D3, and D4 is more than 40

A B C D E

1 ID Date Code Hours PayMethod

2 A123 4/14/14 TRN 20:00 Regular

3 A123 4/14/14 TTT 15:00 Regular

4 A123 4/14/14 TRN 5:00 Regular <----- to make 40

5 A123 4/14/14 TRT 8:00 Overtime <----- 13-5=8, so i have to write down 8 here for overtime

How can I do this?

I want to make a command button for macro to perform this.

View 2 Replies
View Related
Nov 30, 2010

How to report Overtime in a pivot table. Apparently this is more difficult than it seems. Please take a look at the cross posting at [URL]....

I have a sample file there

View 7 Replies
View Related
Dec 15, 2013

I have 3 basic job categories... each of those categories start overtime at a different hour.

So, if employee A is a dock worker, he starts overtime at 25 hours. If employee A is an office worker, he starts overtime at 40 hours and if he is a driver, he starts overtime at 55 hours. So based on that info, I'm wanting my spreadsheet to figure out how many hours each employee has left for the week.

The 2nd part question is how many hours per day is left for the week. Rather than making a separate tab for each day of the week, I'd rather the spreadsheet know what day of the week it is and divide accordingly.

View 4 Replies
View Related
Jun 26, 2009

=IF(C5>40,((C5-40)*D5*1.5)+40*D5,IF(C5

View 9 Replies
View Related
Sep 14, 2006

I'm creating an log to track the total number of hours an employee works in one day and calculate regular and overtime hours worked based on the following criteria: overtime will be >8 hours in one day and >40 hours in one week.

Right now I have it 'mostly' figured out, but under certain conditions, it calculates more than 8 hours in one day for straight time. Is there a way to set a maximum value for the straight time cell and have the difference be added to the overtime cell?

View 6 Replies
View Related
Sep 24, 2012

Timekeeper to tally total hours worked by employee. When doing a pivot for sum total hours worked for FY13 it does not calculate correctly. I understand they formatted that column/custom h:mm but when I change the 8:00 hours to a number I come up with 0.33 .

I am attaching a sample file : sample time.xlsx

I just want them to get a running total of hours worked/pay.

View 5 Replies
View Related
Jul 3, 2014

I'm trying to make a way to track if I've worked more or less than the 39 hours/week I'm paid for. At the end of each week, I have a total of how many hours and minutes that I've worked .

On column C I have what I should work.

On column D I have what I did actually work

On column E I'd like to convert automatically Columd D to minutes for calculation purposes

Column F to know if I worked more or less than what I should've subtracting C and E

Column G to have an ongoing tally to know if I need to work more or less

Column H and I could probably be the same thing. Ideally what I'd like is to have a formula pull the information from column G and put it into workdays, hours, and minutes with 1 workday being 7 hours and 48 minutes.

Since I tend to work too much, I'd like to know if I've worked 3 days too much during 1 month, I can take 3 days off the next month to get everything zeroed back to where I don't owe the company anything and vice versa.

View 6 Replies
View Related