Calculate Overtime
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.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
How To Calculate Overtime
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}
View Replies!
View Related
Calculate Overtime Hours
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 Replies!
View Related
Calculating The Overtime
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 Replies!
View Related
Calculating Overtime
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 Replies!
View Related
Calculation Of Overtime
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 doubletime. (2x) • Any hours over 40 in a week are timeandahalf (1.5x) • Any hours over 48 in a week are doubletime. (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 timeandahalf, and • 6 hours of double time. I’m using the following formulas: ...
View Replies!
View Related
Overtime Calculation On Timesheet
" =(C2 >D2)*MEDIAN(0,D21/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 (06001800) standard rate, day (06001800) overtime rate, night (18000600) standard rate, night (18000600) 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 972003 format.
View Replies!
View Related
Sum Overtime Hours
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 Replies!
View Related
Employee Timesheet With Overtime
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 Replies!
View Related
Counting Overtime With A Twist
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 monfri 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.
View Replies!
View Related
Track Hours Of Overtime Worked
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 Replies!
View Related
Overtime In Semi Monthly Timesheet
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 ....
View Replies!
View Related
Argument Separate Overtime From Regular
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 Replies!
View Related
Calculating Hours Worked With Overtime Clause
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 Replies!
View Related
How To Calculate Combinations ?
I want to see how much combinations are possible when i got 6 numbers..  got this numbers 123456  want to calculate how much combinations of ( 2 ) numbers possible  want to calculate how much of ( 3 ) numbers possible  want to calculate how much of ( 4 ) numbers possible  want to calculate how much of ( 5) numbers possible  never 2 same numbers together (22) or (223) or (2234) may not be in list  How can i make the result visible in kind of list ? I would be nice if somebody knows a good solution..
View Replies!
View Related
Calculate Variances
Just wanted to find out if the formulas in the attached apreadsheet are correct. Formulas from E6 to E10. Also, if you multiply the "new daily target" with "working days" should'nt this give you the "remaining" figure? Currently it's not doing this
View Replies!
View Related
Calculate Totals
I have a spread sheet that is used to review calls placed by a call center. Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call. We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts? My initial thought was something like this: =SUMPRODUCT((A2:A5000=H2),(E2:E5000)) In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.
View Replies!
View Related
Calculate Ratio
I'm trying to create a quote... basically I need to find out about calculating ratios. We have 47 students going on a trip to different venues, we offer 1:10 teachers (i.e. one teacher free, for every ten students going), however some venues offer a different ratio e.g. 1:5 teachers free of charge. I need to somehow create a column where it will calculate the amount charged for the different ratios the venues offered.
View Replies!
View Related
Calculate The Variance
i have a dynamic list of numbers....currently 10 numbers in the list. how can i calculate the variance? i have the upper limit (=MIN(1,(mean+half width)) i have the lower limit (=MAX(0,meanhalf width) i have the mean (avg of all numbers) i have the t value (TINV(alpha, (n1))) i have the half width (t value * SQRT of Var/N) i just don't know how to get the VAR/N
View Replies!
View Related
Calculate If There Is Anything In The Cell
Im wanting to do a sum calculate if there is anything in the cell. Ive tried using the 'count' and 'count if' but both return values of zero. There are words in the cell, rather than numbers. I want it to return the value of cells with data in them, and exclude those with nothing ("") in them.
View Replies!
View Related
How To Calculate Age
to calculate the age from the format date of birth shown below. SQL Data S1Date of Birth2Jun 9 1947 12:00AM3Jan 1 1957 12:00AM4Jan 1 1958 12:00AM5Jan 1 1956 12:00AM6Jun 4 1951 12:00AM7Dec 10 1963 12:00AM8Jun 17 1958 12:00AM Excel tables to the web >> Excel Jeanie HTML 4
View Replies!
View Related
Calculate Hours
I am designing a simple time card. Column D=time in, Column E=time out. My formula in column F to calculate total hours is =TEXT(E2D2,"[h]hrs"). The result is not correct. Example: In at 9:30 out at 5:00 and the calculated total is 4 hours.
View Replies!
View Related
Calculate The Probabilities
How to calculate probabilities... say that I am buying pop bottles that say "1 in 6 wins" or drawing buttons from hats where 5 of them are numbered 15 and the other one is the winner. (Note that you would have to keep putting the buttons back after drawing so there is always 15 and a winner whenever you draw) The possible combininations are easy to figure out... just do 6 to the power of how ever many bottles you buy or how many times you draw a button. So obviously if you draw once, there is 6^1 possible... if you draw twice there are 6^2 (or 36) possible combinations. To figure out how many winning possibilities (win atleast once) there are it's not too hard at this level... you can just list the possibilities: First  Second 11 12 13 14 15 1W (1 winning combination) 21 22 23 24.................................
View Replies!
View Related
Calculate The Value In The NPV
I have two columns in a payment schedule (which adjusts according to certain user inputs) that I need to use in my NPV calculation. The first column is the Total Payment and the second is Inducements. Therefore each value in the NPV calc. needs to be the sum of a given period's payment and inducement (but i don't want/have a separate column which calculates the sums). The number of periods adjusts with the users input of Term. There also may be periods where there is a payment but no inducement.
View Replies!
View Related
Calculate The Percentage
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file. The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.
View Replies!
View Related
How To Calculate Time
WHen I was a beginner at Excel, I came up with the following formula to calculate the time difference between two "time" values. Since I was too lazy to add a ":" between my hour and minute, I had decided to simply enter the start (column B) and end time (column C) as a military time (e.g., 0100, 1230, 1500, 1930, 2300, 2359). The following formula would be in Column D. =IF(C7
View Replies!
View Related
How To Calculate Date
how to calculate the date for one year. If i have 24Jun08 (A1) i need to calculate 24Jun09. If i do =A1+365 it gives me an error (the little green thing in the corner of the box) is there a way to do this without the error showing up.
View Replies!
View Related
Calculate Total
I need a formula that will calculate the total of the items in column "W" when the row directly below it contains a "1" in column "V". It will need to calculate the totals from that row all the way up to the next row that contains a "1" in column V. Example: In X14 (where I will put the formula) it would total W9 through W14. However the formula still needs to be able to calculate the total if there are more or less cells to sum. So that the same formula could calculate that in X8 it will total W5 through W8.
View Replies!
View Related
Formula To Calculate A %
I use this formula to calculate a %, but naturally when there is a 0 excel returns the #DIV/0! error. Can someone fix my formula so the error gets replaced with a 0 in the formula the 0 would be in cell W2 =IF(N2="yes",BN2/W2,0.5).
View Replies!
View Related
Calculate Text As Value
trying to calculate a text as value in a formula. In cell A5 (please see the excel attached) I have a weighted formula that is supoosed to return a final result based on the drop down from cells B5 to V5. Drop downs give the option of choosing a value. Everything is going fine as long as you select a value (5, 3 or 0). In cells H5, J5, K5, R5, S5, U5 there is also the option of choosing NA. I am trying to get the formula to calculate NA as being equal to 5. Basically, if for the respective parameter someone choses NA, the final result in cell A5 should not be influenced. If all the parameters are 5 and only one has NA (any of the cells H5, J5, K5, R5, S5, U5), the final result (in cell A5) should be 100. The same applies if all parameters are 5 & more than one cell or all the cells that have the NA options (H5, J5, K5, R5, S5, U5) are NA.
View Replies!
View Related
Calculate Overlap
Here is my data Range A1:L8 1234567891011Overlap111122275%22275%22250%22225%2220%2220% I am trying to figure out a formula in L3 which is the calculation of overlap of region1 and region2. The top row is the number of days. What formula can I use to calculate the percent overlap? The numbers in column L show the expected result. (Using XL 07)
View Replies!
View Related
Calculate Workbook
I'm opening 2 workbooks and I'm on Manual Calculation option. I wanted to calculate only workbook A. The standard button only give me the option of calculating a worksheet or all open workbooks (F9).
View Replies!
View Related
Calculate A Certain Weekday
I need to set up something that "calculates" an orders next delivery date. We deliver on Tuesdays, Wednesdays, and Thursdays for different stores. IE. Store 1  Today's DateWednesday 10/28/09 = Next delivery date is Tuesday 11/3/09. Store 2  Today's DateWednesday 10/28/09 = Next delivery date is Wednesday 11/4/09. Store 3  Today's DateWednesday 10/28/09 = Next delivery date is Thursday 10/29/09.
View Replies!
View Related
If Cell Has Value Calculate
I am setting up a spread sheet to compare an invoice between 2 suppliers. It has formulas throughout and a total column. If no data is entered in cells then total column has "value" in it. How can I eliminate the"value" while still keeping formulas in place. Not all cells will have data in them on every instance. But I still need all formulas to be saved
View Replies!
View Related
Calculate Within Ranges
I have a range of cells and when an amount is entered into a cell I would like it to pick which row to pick from and do the sum from that row. for eg if I was to put $201,000 I want it to pick the row with $200,000 to $540,000 range and use the sum from that row. eg below Taxable ValueRates Percent $0$200,000 $00.0% $200,000$540,000$2000.2% $540,000$900,000$8800.5% $900,000$1,190,000$2,6800.8% $1,190,000$1,620,000$5,0001.2% $1,620,000$2,700,000$10,1601.8% $2,700,000> $29,6003.0% the sum I have for the rows is ((E3A3)*D3+C3) but somehow need to have it choose from ranges and use appropriate sum.
View Replies!
View Related
Calculate Discounts According To Value
i dont know if IF statements can do this BUT if you look @ this image! what it is i need the spreadsheet to automatcally work out the discount for xample "Example Jhon" spent 29.99 and i want the spreadsheet to work out the discount automatcally for 15% i know IF statmenet can do this but i need it to work out what discount theyll get Each price is going to be diffrent for example if someone spend 50 they would get 25% off i need excel to figure out what discount thell get automatcally
View Replies!
View Related
