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 RepliesWe 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.
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
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.
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.
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.
=IF(C5>40,((C5-40)*D5*1.5)+40*D5,IF(C5
View 9 Replies View RelatedI 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.
I have employees hours worked entered in multiple rows. Columns are Date, Name, Hours worked; then a column that calculates the week number.
The data is entered each day so the Names are not in order and I'd rather not have to resort by name each week.
I'm collecting that data along with several other fields in a pivot table, then I have a summary sheet that uses GETPIVOTDATA formulas to compile a ton of statistics.
I want to be able to filter the pivot table on any given number of weeks. I need to be able to calculate overtime (greater than 40 hours in a week) for each name over the filtered weeks. So I could want to see OT for weeks 4, 5, 7 combined.
I am trying to calculate over time based on shift time.
For example: Regular shifts are between 7:00 AM to 3:00 PM (Monday thru Friday). Anything between those hours and on those days should be considered REGULAR TIME. Anything between 12:00 AM to 6:59 AM or between 3:01 PM to 11:59 PM should be calculated as OVERTIME.
Anything on Saturday or Sunday should be calculated as OVERTIME as well.
calculate overtime in California. I found a few previous posts, but none that meet all of the requirements. Employees get overtime in CA for:
More than 8 hours are worked in a day (up to 12)
More than 40 hours are worked in a week
Hours worked on the 7th consecutive day (up to 8)
Employees get double overtime for:
More than 12 hours are worked in a day
More than 8 hours worked on the 7th consecutive day
To eliminate the the 7th day issues, I am just using helper cells for hours worked on the 7th day of the work week. So far, what I have is what I found in a previous post:
=MAX(0,SUM(A13:A26)-40-SUMIF(A13:A26,">8")+8*COUNTIF(Daily Total Hours,">8"))+SUMIF(A13:A26,">8")-8*COUNTIF(A13:A26,">8")
This will calculate the hours of overtime over 40 in a week and 8 in a day, but will not differentiate between hours 8-12 and hours 12-?
I am trying to formulate a formula that will calculate overtime hours worked.
Now standard hours are 17:30pm - 20:45pm. Anything outside these hours are overtime. If the start time is 18:00pm then the person is still paid from 17:30pm @ standard rate regardless.
Now I am trying to work out a formula that will cover hrs outside of the standard hrs AND hrs unworked but paid for.
see attached! September tab {blue highlighted cells}
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.
=IF(a9>40,(a9-40*1.5))
Obviously this is not correct because the result is FALSE.
I have a time sheet for my employee's that I need to calculate their overtime in 1.5x and 2x rates.
Their overtime totals are done in individule columns from D33 to S33. The first 4 hours per day are charged at 1.5x and anything over that is 2x. I want to show the 1.5x in one box and the 2x in another. I do believe that I need two formulas one in each of the boxes where the final totals would go.
Here's an example, in columns D37 to D41 the employee has worked 12, 14, 9, 16 and 14.5 hours. so that's 17 hours @ 1.5x and 8.5 @ 2x.
I use Excel 2007, and I need help with an overtime calculator. It pertains to a specific wage order, which has three basic principles:
• Any hours over 16 in one day are double-time. (2x)
• Any hours over 40 in a week are time-and-a-half (1.5x)
• Any hours over 48 in a week are double-time. (2x)
I worked 5 hours on a Monday, 18 hours on a Tuesday, 18 hours on a Wednesday, and 13 hours on a Thursday. (I work in a residential group home, so 24 hour shifts are common). That totals 54 hours, and the correct overtime breakdown should be:
• 40 regular hours.
• 8 hours at time-and-a-half, and
• 6 hours of double time.
I’m using the following formulas: ...
Each employee has a different plan time each day of the week.
Mon - 9 Tues - 9 Wed - 7 Thur - 8 Fr - 7
Overtime is really counted after 40
There are 5 sheets for each day of the week mon-fri lets say sheet1..2...3...etc, on each sheet there is a column a with plan and column b with actual hrs. What I'm looking for is a summary sheet for each employee to see where they trend in OT after each day so...
person1 works 10 hrs on mon with a 9 hour plan thus 1 hr trending OT. On the summary page person1 would now show 1 hrs of OT. Now if they would work 9 hrs on tues it would still show 1 hr of OT. On wed however they worked 6 hrs so now the summary page would 0.
i am creating a weekly time sheet for my company.the problem that i have is when the persons time reaches 40 hours, the time needs to be calculated in the overtime field. this is really tough for me when the person reaches 40 hours in the middle of the work day. I cant figure it out. i have attached the spreadsheet if you would like to look.
View 2 Replies View Related" =(C2 >D2)*MEDIAN(0,D2-1/4,1/2)+MAX(0,MIN(3/4,D2+(C2 >D2))-MAX(1/4,C2)) "
approach to sort out Day/Night Hours. Its bomb proof!
A new situation demands overtime payments......start and finish time can be any time day or night (crap job!), overtime is payable after 8 hours. Thus I have day (0600-1800) standard rate, day (0600-1800) overtime rate, night (1800-0600) standard rate, night (1800-0600) overtime rate.
So, starting at 1400 and finishing at 0100 give 4 hours day std + 4 hours std night + 3 hours night o/time; whereas starting at 0200 and finishing at 1300 gives 4 hours std night + 4 hours day std + 3 hours day o/time.
I'm using Excel 2003 and 2007 so use the Excel 97-2003 format.
We are working on a spreadsheet that would project what our labor cost would be for next week. I need some help in figuring out how to calculate overtime when an employee reaches 40 hours.
View 9 Replies View RelatedI 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 RelatedI 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.
spreadsheet has daily hours worked per employee and a weekly total. What is the formula to take the weekly total per employee and separate the regular hours (first 40) and have the overtime show up in the next column (without getting negative numbers for the totals under 40).
View 4 Replies View RelatedBasically I have a timesheet as follows:
Basic Hours Worked
Overtime Hours Worked
Day
Time In
Time Out
Hours Worked
Time Decimal
Lunch Hour
Number of hours
Mon
8:56:00 AM
4:50:00 PM
07:54
7.90
[Code] .......
To determine the hours worked from the times input I am using the following formula:=
HOUR(D65)+MINUTE(D65)/60
These formulas seem to be working fine but what I am stuck on is deducting and adding lunch hours and overtime hours.
The first problem I have is for example if an employee were to type the figure 1 in the Lunch Hour column for each day lunch is taken I would like it to be subtracted from the bottom total.
Also if any overtime were input on any day I would need it added to the totals.
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?
I’m trying to take an existing employee time sheet in Excel (Office XP) that has no formulae whatsoever, and add the appropriate formulae so that all an employee needs to do is enter the daily start and end times and the time sheet will calculate daily, weekly, and overtime hours worked. Among others, some of the problems I’m having are:
I need to keep the original format (though I've added a few columns).
Overtime in the State of Texas does not apply until after 40hrs have been worked. Then any daily hours over 8 can be applied retroactively. So I need a timesheet that shows overtime as regular hours worked until 40 hours have been reached, then separates the daily overtime from the regular column and places it in a daily overtime column. Shouldn't be too hard to find...Right?... Actually, that’s been quite easyexcept for weekends. Saturdays and Sundays are usually overtime but not always.
The real problem is the beginning day of the pay period, if a pay period begins on any day other than Monday (Wednesday, for example,) then weeks one and sometimes three can never equal 40 hours each unless the assumption is that the days worked in the same week but prior or subsequent period are worked at 8 hours each. The formulae must make this assumption. How do I write a formula that assumes an empty cell actually has a value? :o
I know that it’s difficult (if not impossible) to offer any suggestions without seeing the time sheet itself, so, If it would be helpful, and anyone has any suggestions. I’ve uploaded the week one of the timesheet as it stands now.
If you'd like to see the entire worksheet I've uploaded it to ....
I am looking for a formula to calculate overtime on a weekly basis, entered daily, on a sheet with multiple weeks. It need it to calculate the overtime each week.
View 8 Replies View RelatedI've been trying to automate the calculations on timesheets and having some trouble. I've attached a rough copy of part of the sheet to this post.
Here's where I'm struggling:
The timesheet has columns for Ins/Outs as well as Reg Hrs (Should never be more than 8), OT 1.5 (Standard 1.5 OT pay for anything over 40 in a week), OT 1.0 (More than 8 hours worked in a day but Vacation/Sick/Holiday/Funeral time keeps it below 40 hours actual work time that week) and then columns for Vacation, Sick, Etc.
Now I'm confident that my Regular Hours works with the following formula:
=IF((((C3-B3)+(E3-D3))*24)>=8,8,((C3-B3)+(E3-D3))*24)
If statement calculates how many hours worked, multiples it by 24 to put it in an hour format and checks whether it's greater than or equal to 8, if so then only show 8 (since any additional hours worked would need to be in the OT 1.5 or OT 1.0 category depending on the circumstances). If false, returns how many hours worked.
However, I am having some issues getting my OT 1.5 and OT 1.0 categories working correctly. The OT 1.5 column needs to check whether the actual worked hours is over 40 without Vacation/Sick/Funeral, etc included. The OT 1.0 column needs to show any additional hours worked beyond 8 each day but less than 40 over the whole week because of holiday/sick/vacation, etc.
So, if I worked 10 hours on Monday and there isn't any sort of paid leave time that week (and I work normal 8 hours the rest of the week), then Monday would show 8 hours in Regular worked and 2 hours in OT 1.5. Here's what I'm currently using for a formula (that isn't working...my latest addition was the OR statement):
=IF(AND((((C3-B3)+(E3-D3))*24)>8, OR(($F$12-$K$12)>40), $K$12=0), (((C3-B3)+(E3-D3))*24)-8, "")
If I worked 10 hours on Monday and there is some sort of Holiday (and I don't actually work over 40 hours that week not including the 8 hours of Holiday pay), then Monday should show 8 hours in Regular time and 2 hours in OT 1.0. Here's what I'm using for this formula (that also isn't working well and I hadn't started working with the OR statement here yet since OT 1.5 wasn't working) :
=IF(AND((((C3-B3)+(E3-D3))*24)>8, $K$12 > 0, $F$12<40), (((C3-B3)+(E3-D3))*24)-8, "")
testsheet.xlsx
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
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.