Formula To Take The Total Manhours Over 40 And Enter Them Into The Overtime Cell
The formula =SUM(E5:E39)-SUM(E45+F41) is what I'm using to enter my total man hours for the week (E5:E39) and subtracting my overtime(E45) and vacation hours.(F41). I have to enter OT and vacation on the discription cells to get the numbers to enter into the proper cells. I would like to use a formula to take the total manhours over 40 and enter them into the overtime cell.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Offset Formula: Getting N/A In Cell When Enter Formula
I have attached a sample of the spreadsheet. The cell/cells in question are f32:f36 on the first tab. Question #1 how this formula works or what it does. Question #2 Is when I enter the required information in cells a32:e32 it enters an N/A in cell f32. When I than click on cell F32 and than click on the formula bar and hit enter the information populates.
View Replies!
View Related
Enter Data In A Formula Cell
Is there a way to add data into a cell that already had a formula? Lets say in cell A1 we have a formula like "=A1*A2". Would I be able to some how enter a value into cell A1 without changing the formula? Example: enter value "5" into A2 and value "10" into A1 which would result in A1 value being "50" after value is entered. I know that this creates a circular error but is there any other way to accomplish this?
View Replies!
View Related
Enter Formula To Cell With Macro
I am trying to make a very simple macro, that when it is run, it inserts a formula into the selected cell. But I am having problems with quotation marks etc... As it doesn't understand the 3rd and beyond quotation marks. Sub Macro() ActiveCell.FormulaR1C1 = "=IF(A1<3000,"Small", "Large")" End Sub I'm sure there may is a way of coding it in VBA properly, but I plan to use this method for a range of basic formulas. The idea is this is saved in the Personal Macro Book so I can start a column of formulas by running a Macro rather than typing it in (the example above is a simplified formula, the actual one is fairly long) - then it can just be dragged down on every Workbook I need it in.
View Replies!
View Related
Copy Formula From Previous Cell To Next Cell When I Enter Something
I want to copy formula from previous row to next cell when i enter something in perticular cell. i.e --Colomn A --- Colomn B -- --------Colomn C 1 01-09-07 ----- John ----------=vlookup(b2,$s$1:$t$10,2,false) 2 01-09-07 ----- Smith -------- =vlookup(b3,$s$1:$t$10,2,false) 3 4 5 Now if i enter date in cell A3 then cell C3 should be automatically filled/copy formula as celll C2. and so on...... then if i enter data to A4 then cell C4 should be automatically filled/copy formula from cell C3. I have also attached example file.
View Replies!
View Related
Calculating Manhours/labor Cost In 2003
I am having trouble trying to calculate cost for a specific task. I know this is something simple and I am going to kick myself when it gets solved, but I have total brain lock right now! Here is the example of what I am trying to do. A B C D E F # of people start finish time man hours labor cost 3 1:35 2:05 :30 1.5 $15.00 I am entering the values in A, B and C, with B & C formatted as TIME. D is calculated by =(C3-B3), but I am lost trying to calculate E and F.
View Replies!
View Related
Total Occurences: Formula That Shows The Total Payments Recieved For A Particular Month For A Particular Product
I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell. Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example. I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
View Replies!
View Related
Formula To Total Two Cell Ranges
I have a sheet composed of daily sales. Within this sheet I have averages and totals automatically calculated for each week. I would like to set up a cell that keeps a running total of sales for the entire year as they are entered. I can set up the formulas for each week grouping and they work fine, but I can't seem to get it right for the entire sheet. Here are the formulas: =sum(b2:g27) =sum(j2:o27) These formulas work fine by themselves, but when trying to write one formula to calculate the total for both cell ranges I keep getting errors. I just want one formula to calculate the running annual sales as they are entered. I know I'm missing something simple here, but I don't know what it is.
View Replies!
View Related
Enter Formula Using A Macro
I am trying to write a macro which will get values from column B and C and print the result on column D using a simple function like the one before: D2 = "text" & B2 & "text2" & C2 & "text3" I need this to be done in the macro, so that when I click the button, it will automatically create column D. Column B is formula and column C is constants. I tried something like the one below:
View Replies!
View Related
Enter Array Formula Via VBA?
I have following Array formula in Cells N1:N3. HTML =SUM(IF(O$4:O$1000="Total I/O Points",N$4:N$1000,0)) Each formula has different text in "". There are a few macros in this workbook that delete and/or insert rows. When that happens, the formulas listed above get modified with different references. How do I restore the formulas back to the same range reference [O$4:O$1000]?
View Replies!
View Related
Enter Current Workbook Name In Formula
I have the formula: =HYPERLINK("[Book1.xlsm]'Sheet1!A1","To Reachback") I may however change the name of Book1, therefore rendering the hyperlink formula useless. Is there a way to enter something in place of Book1 that will return the current name of the workbook? If there isn't, I will have to create a VBA program to update the hyperlinks, and will therefore have additional questions about that shortly.
View Replies!
View Related
Manually Enter % And Update Price (Formula)
I'd like to be able to add 10% to column D and have the prices in A, B, C change accordingly. Is there a formula for this? A B C D 2009 Distributor CASE Price2010 Distributor CASE Price2010 Distributor EACH PriceVariance from 2009 price135.00148.000.0592108.25100.000.040062.8875.006.250066.6096.008.0000 PS: Right now I have it set up working the opposite way, the prices are entered and my formula tells the user by what % the price has changed. The user wants to be able to tell the formula what % is desired and have the prices change accordingly. The formula I am using for the way column currently calculates is: =IF((G2=0),"",(((G2-F2)/F2)*1))
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
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 Replies!
View Related
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
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 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: ...
View Replies!
View Related
Alternative To CTRL-SHIFT-ENTER Array Formula
This formula works great and does everything I need it to do, BUT unfortunately I have since discovered that it is not compatible with several end applications that I am using with my spreadsheet. Can somebody please suggest an alternative to the array formulae, as written in cells: O6, Q6, S6 & U6, while still giving me the same end result?
View Replies!
View Related
VBA Formula That Require Control+Shift+Enter
VBA automation - Excel added the formula - but they all return "Not Found". After the VBA automation - I visit each cell, see the formula is correct and then press Control +Shift + Enter; then the correct value displays! Of course, the curly brackets also appear in the formula bar. In VBA I tried to use the: objXL.ActiveCell.FormulaArray = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat ..... The Excel Formula bar for the cell is just empty (blank) Using only the Formula property: objXL.ActiveCell.Formula = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat ..... The correct formula is created in each cell, but. Until I visit that cell and use the Control+Shift+Enter - the lookup will not work. It is proof that the right formula is there, without the curly brakcets. In Excel - my vba code successfully constructs these formulas: In essence: It checks for an error and prints "Not Found" if no match is found in the check. It test for two values in a row - matches them to two columns on a row in another worksheet, and returns a third value for the matches of the same row.
View Replies!
View Related
Overtime Calculation On Timesheet
" =(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.
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 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.
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
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
Sorting/total Formula
I have to create a summary sheet of stock code quantities for work and i know there must be an easier way to do this then totalling each style and colour by a basic "=sum" formula. At present the information is by style then colour break down and i want it excel to automatically calcuate the total per style number if possible EXAMPLE: Style# DESCRIPTION COLOUR QTY 12345 Hooded Sweat Black 3 12345 Hooded Sweat Blue 11 12431 Treasured Tee Pink 9 12431 Treasured Tee Yellow 6
View Replies!
View Related
Cumulative Total Formula
D17 is a cumulative dollar total (year-to-date) of the monthly changing dollar amount in D5. Each month I manually add the D5 figure to D17. Is there a formula that will automatically update the D17 cumulative total with the everchanging D5 monthly figure?
View Replies!
View Related
Running Total Formula
I've attached a sample workbook. I'm trying to have a running total on column F once column G is completed with a ship date. The formula I have right now is =IF(G2:G55<0,SUM(F2:F22),0). but it's coming up w/zero.
View Replies!
View Related
Rolling Total Formula
Whenever I add new data I need two cells of the old data to not be counted. This should continue until old data isn’t being counted anymore. EXAMPLE: New Data entered Cell C36:E36 (148, 147, 214) Old data shouldn’t be counted anymore from Cell C67:E68 (137, 152, 156, 94, 117, 158) Total before New data Entered: 14066 After New data Entered: 13761 This formula is required for a total of 16 weeks or until all the old data isn't being calculated anymore in my rolling total. I only need one total the three examples are just showing what the total should be calculated. Sheet1ABCDEFGHIJKL3163253343433521472142103611481472141481472143732192168158192168158192168158383115417113715417113715417113739301771141681771141681771141684029114145164114145164114145164412816816615316816615316816615342272071441512071441512071441515217136160143136160143136160143531615710516815710516815710516854151031201661031201661031201665514144133153144133153144133153561313016013313016013313016013357121711531411711531411711531415811119157186119157186119157186591012111312612111312612111312660913615715213615715213615715261812118813412118813412118813462711712715411712715411712715463613913718213913718213913718264515213615115213615115213615165419313814719313814719313814766319420814719420814719420814767213715215613715215613715215668194117158941171589411715869Examples140661376113305Excel 2007
View Replies!
View Related
Getting The Total From The Result Of The Formula
I have already an existing formula to get only the amount from the previous cell. From a6 to a30 it contains the word "11/05/09 press release $100", so in b6-b30 this is the formula. =IF(ISNUMBER(SEARCH("press",A6)),RIGHT(A6,LEN(A6)-FIND("$",A6)),"") until b30 the problem is in b6:b30 the 100's were not a number you have to copy and paste to other cells using paste special then values then click the smart tag and convert as a number to get all the total from b6 until b30.
View Replies!
View Related
Copy A Formula Down And Total
I have a sheet that in Columns U and V I have a formula. Below is part of my macro. What I need is a quick easy way to copy this down in the macro based on colunm S. When colunn S ends it should stop copying the formula down and total each colunm. Range("U2").Select ActiveCell.FormulaR1C1 = "=SUMIF(C[-6],RC[-2],C[-9])" Range("V2").Select ActiveCell.FormulaR1C1 = "=SUMIF(C[-7],RC[-3],C[-8])"
View Replies!
View Related
When A Date Entered On A Specific Cell, Automatically Enter A Text In Other Cell
I have 6 Headings in excel named... "A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1. There are two projects. Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E. My Specification follows... 1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
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
Running Total Formula For Inventory
I am creating a spreadsheet for inventory use. I want to have a running total in (1) cell based upon a new/different number being entered into a different cell. Column F, Row 7 (This will be a new/different number entered every day) Column G, Row 7 (Begins with a starting total of 2545) I need Column G to add to it whatever number is entered into Column F, Row 7 and to keep the new total (ex. enter 10 into Column F/Row 7 & the total of Column G/Row 7 becomes 2555; if I then enter 20 into Column F/Row 7, Column G/Row 7 adds 20 to the original 2545, not the new total of 2555). Column G is not keeping the new total, it reverts back to the original number of 2545.
View Replies!
View Related
|
TRACKER
