TimeSheets :: Work Out Formula For Working Hours
Want to work out formula for working hours If Mr Smith is booked to work I must pay him a min of 8 hours pay at rate A then upto 10 hours still on rate A but after that he gets rate B I need to enter his data on a daily spreadsheet
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Timesheets With Day And Night Hours
I have a timesheet that the manager fill in every night for the workers, simply it has a start time and end time and it calculates the hours worked (all times are entered in quarters of an hour ie 9.25 for 9:15 etc). At the moment i can work out if a person is on day or night with:
View Replies!
View Related
Formula That Allows Me To Type In Their Hour Work If It's Greater Than 40 Hours
I am building a spreadsheet to manage 15 folks wages to be able to know how much money has been spent. We routinely work 40 hour weeks and have a sheet that automatically multiplies hour work by person (Say A1) times their hourly rate (say A2) My question is how could I right it a formula that allows me to type in their hour work (A1) if it's greater than 40 hours? Say A1 is 40, A2 =$20.00, A3 =(A1*A2) $800.00 If the A1 is 43 what would I put into A3 that would automatically multiply the additional hours over 40 times 1.5 A2
View Replies!
View Related
Formula For Date And Time Difference Using 8am5pm Work Hours
I need to enter a formula that calculates the time a report is received from the time it was recorded in our database. therefore, it needs to exclude non working hours. here are the fields: A1 2008/10/10 16:30 B1 2008/10/11 09:30 C1 8:00 D1 17:00 A1 = report received B1 = reported recorded in db C1 = work day start time D1 work day end time Where the answer should = 2 hours. I am not an experienced excel user and so far the only formula I have now is: =TEXT(B1A1, "d:hh:mm") And how do I account for weekends?
View Replies!
View Related
Difference Between Dates And Times In Days , Hours Mins ( Working Hours )
The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie : Difference between 02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes =(INT(A3)INT(C6))+MAX(MOD(A3,1)MAX(MOD(C6,1))) The following displays it in the Hrs and Mins format =TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))
View Replies!
View Related
Add The Hours Someone Is Scheduled To Work
I am looking for a funtion that will help me add the hours someone is scheduled to work. The problem I am having is the format. Here's the format: The days of the week are Mon  Sun (D3J3). L3 is a total of the hours they are scheduled to work for the week. Cell D3 is 82. Cell E3 is 93, etc. for the rest of the week. What I am looking to see if possible is in L3 take the row, Take D3 at 6 hours, E3 at 6 hours, etc for the rest of the row, and add them together for the total hours in L3. I have attached a small example file of what I'm trying to do. The format I am looking to use for the hours is as described above.
View Replies!
View Related
Subtracting Hours In A Work Schedule
I am working on a monthly employee work schedule that has 2 stores. All employees work at both stores and the boss wants 2 separate schedules. I have attached what I have started, which at the moment I am stuck. I would like to have the Row 5 total hours to always reflect (subtract) a 30 minute lunch. I know it would be easier to have another column, but the boss wants it this way, I also need the total hours in column H to reflect the total of each day (with the subtracted lunch break). In this column I am having the figures turn red if over 40 hours and green if it under 40 hours. With having 2 stores with the employees working at both, I need to somehow have the hours of the employees add up from one store to the other (a running total, if you will). Lastly, if I have an employee not scheduled for a day or need to put other data such as, VAC, HOL or blank cell etc in a cell, how can I get this to work and not have the formula go nuts when it is not in a time format.
View Replies!
View Related
Removing Non Work Hours From Calculation
I am trying to work out the minutes elapsed for a call monitoring system. The hours monitored are between 05:30 and 19:00  so if a call gets logged outside of these hours then the minutes calculated will be calculated from 05:30 the same day if logged on or after midnight or 05:30 the next day if logged before midnight (ie the next 05:30).
View Replies!
View Related
Calculating Time To Track Work Hours
I can't figure out how to do a proper formula for calculating time. For instance I have one column that says "Time In", the next is "Time Out" and the other is "Lunch Time". My calculation needs to be this: Find the total hours between the Time In and Time Out and then Minus the lunch time to get total hours worked for the day. The only way I can get this to work is using 24 Hour time format. Is there another way?
View Replies!
View Related
Work Rota/Roster Calculating Hours
I am currently trying to create a rota / rosta for work which will show people what hours they are required to work, in a format similar to below: NAME  SAT  SUN  MON  TUE etc > Person A 8:00  17:00  13:00  22:00  OFF  8:00  13:00 Person B OFF  17:00  22:00  8:00  17:00 OFF  This is simple enough to create but I also need Excel to calculate how many hours each person is working in a week and the total number of hours used per day and per week. I found out that I can do this using a formula to work out hours and minutes between two times. However what I would also like Excel to do is deduct the unpaid breaks which the person is entitled to, however this depends on the times that they work, i.e. in the example above Person A would be entitled to a 1 hour unpaid lunch on Saturday, a half hour unpaid tea on Sunday and no unpaid break on Tuesday. Therefore 1 hour would be deducted off the Saturday total hours, 30 minutes would be deducted from the Sunday total hours and nothing taken off the Tuesday.
View Replies!
View Related
Adding Working Hours
I want to write a macro to add business hours to a datetime value, excluding weekends, nonworking hours and holidays. e.g., I have a value (datetime) of "18/12/2009 11:30:00 AM", I want to add 10 hours to this, so as to get the result as "19/12/2009 12:30:00 PM".
View Replies!
View Related
Calculate Working Hours
i had a sheet with the employee name,Working Place, Designation,Starting Time & Ending Time of each employee working in a Department. I want to track the no of hours each employee worked in the Format 1 Hr: 45 Mins . i tried to get the result by substracting the ending time from starting time and then formatted the cells using the TIME FORMAT from the FORMAT Menu>Format Cells > TIME. But i m not getting the required result. I want a vba code or formula for extracting the no of Hrs & Mins the employee worked using the above formula.
View Replies!
View Related
Working Hours Between 2 Dates
I have a spreadsheet with various date fields and next to those time fields. I need to calculate the amount of working hours between the 2 lots of values for example Cell A has "22/02/2008" Cell B has "16:44:00" Cell J has "25/02/2008" Cell K has "08:59:00" The answer i'm looking for is 01:29:00. The working hours are Monday to Friday 07:30  16:00. This is what I have so far and it gives me the total hours between the 2 dates but not the working hours. =IF(J10A10=0,K10B10,((J10A10)+(K10B10))) (Formatted with [h]:mm:ss). I have tried using workingday() but can't get the desired result
View Replies!
View Related
Work Days And Hours Between Two Dates & Times
I am creating a 'HelpDesk Issue Logger' and I am trying to calculate the Network Days and Network Hours between two dates with times. I want to know how many business days and hours are between the two days to give me a TURE 'Response Time.' I have been to several sites and forums looking for the answer, but I have not been able to find a solutions that works for me. Please find attached a 'stripped' down version of my project. Variables:  'Date Received' (H11)  'Date Actioned.' (I11) 'Response Time' (K11) Constants: Work Days = Monday to Friday Work Hrs = 8:30 AM to 5:30 PM (no lunch break) Public Holidays = (AC13:AC30) Formats: Date Received: dd/mm/yyyy hh:mm AM/PM Date Actioned: dd/mm/yyyy hh:mm AM/PM Response Time: d  hh:mm If there is someone out there wiling to put me out of my missery with this one, you will have a friend for life.
View Replies!
View Related
Calculating Working Hours Between Two Dates
How should I calculate working hours between two dates? Say if start at 26 july at 15:00 and finish at 29 july at 10:00, the function should return 4 hours because the working hours are from 8am to 5pm (8  17), and there is a weekend between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also, if have to add say 8 hours to a date, how should I calculate the result? Also this function should aware of the working hours and holidays, so it should ignore those times.
View Replies!
View Related
Calculate Normal Working Hours
I am currently using Excel 2007 and I am trying to find a good working formula for Normal Hours worked: I have the following code for Total Hours, Time and a Half and Double Time hours but I can't seem to get the right formula for working up to 8 hours. Whereas C11 = Total hours added for the day. =MOD(SUMPRODUCT(C8:C10C7:C9),1) =Total Hours Worked =Normal work hours between 0 to 8 hour work day. =MIN("04:00",MAX(0,C11"08:00")) =Time and a Half hours over 8 hours worked. =MAX(0,C11"12:00") =Double Time hours over 12 hours worked
View Replies!
View Related
Night Vs Day Working Hours
I have a spreadsheet that calculates the total working hours of our warehouse staff, overtime, etc... using some good advice that has been published here before. Cell C1 : starting hour Cell D1 : ending hour The hours are filled in, using 1904 system, without date as 08:00, 12:00, 21:00, etc... total hours is calculated as follows: =(D1C1+(D1
View Replies!
View Related
Work Out A Formula For My Spreadsheet Which I Use To Work Out Cutting Lists For Timber Frames
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View Replies!
View Related
Calculate Only Working Hours Between Two Dates Excluding Weekends
How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8  23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates.
View Replies!
View Related
Calculating Task Completion Time In Working Hours
The below is for Excel 2003. I want to set up a spreadsheet that can show my work is completed within a service level agreement, based on working hours of 95 MondaySaturday. I want to be able, for example, to log that a piece of work is reported at 09:00 on a Monday and completed at 10:00 on Monday and for excel to calculate that as 1 hour until completion. Fine so far. But what if that job was closed at 10:00 two days later? Based on an 8 hour working day, that should be 17 hours. And what if a bit of work comes in at 17:00 on the Saturday. No one works the Sunday. Say the job is completed at 10:00 on the Monday. that should calculate as 1 hour to completion. The SLA I'm setting is 4 hours.
View Replies!
View Related
Calculating Elapsed Time,respecting Working Hours.
I am looking for a formula to calculate time from one date and time to another. The only catch is I only want it to use times during specific times. Example Start 5/1 8am and end 5/2 8am. The working hours are from 7am until 4pm and from 8pm till 5am. In this example the solution should be 17 hours. How do I set up a formula to respect only working hours (also excluding weekends except for Saturday early am from midnight until 5am. Sorry if this is confusing, I am trying to use IF statements but I’m fighting a losing battle.
View Replies!
View Related
Subtract X Working Hours From Date & Time
I am trying to subtract 8 hours from a date/time. What I need to do is to capture that lead time using only weekdays, and working hours of 8am5pm. For example: Date/Time 6/23/2008 9:18AM (Monday) Subtracting 8 hours from this scenario would give me 6/20/2008 10:18AM (friday)
View Replies!
View Related
Calculate Working Hours Between 2 Dates Including Saturday
I need to calculate the total WORKhours (09:0017:30) between two date/timestamps; Including Saturdays but excluding Sundays & Holidays. I can get this working excluding Saturdays (formula below) using NETWORKDAYS however the call centre work six days a week MonSat. A1=dd/mm/yyyy/hh:mm A2=dd/mm/yyyy/hh:mm DayEnd= 17:30:00 DayStart= 09:00:00 HolidayList= "Currently Blank" =(NETWORKDAYS(A1,A2,HolidayList)1)*(DayEndDayStart)+MOD(A2,1) MOD(A1,1)
View Replies!
View Related
Macro Not Working Except In One Tab, Must Save And Reopen To Work In Others.
I have a wierd issue. I have this macro for a workbook i use and if i run the macro in the first tab it runs and works fine. When i select the other tab and run it it gives me a debug error. If i close the worksheet save it and reopen it and run it in the other tab it runs fine. So in otherwords i have to run the macro in the tab desired, save it , and always close and reopen the file to run it in the other tabs. I know the code is correct because it runs flawlessly without errors but i must be missing something. Also i currently have the macro to run on the active tab/worksheet. Can i have it run on multiple tabs? i have 15 20 and 30 tabs in this example file but in my real file i have many more milage intervals ( some tabs i would not want to select ). It gets tedious having to select one tab at a time, run the macro, rinse and repeat. For Example Run Macro First, in the first tab. It works perfectly. Go to the second tab and run the first macro. It gives a debug error. If you save the file and reopen it and go to teh second tab and run the macro again it works.
View Replies!
View Related
UDF Working When Called From Sub, Doesn't Work As Worksheet Function
I have written a userdefined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within. For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found. My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.
View Replies!
View Related
Calculate Pay For Shift Work With Different Rates Based On Shift Hours
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyymmdd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the ifthenelse statements for calculation seems to be wrong!! examples: start = 20080101 09:15 , end = 20080101 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333 start = 20080103 21:05 , end = 20080104 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000 start = 20080202 19:00 , end = 20080202 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000 Function prod(st As Date, en As Date) As Double Dim shour As Integer Dim smin As Integer Dim ehour As Integer Dim emin As Integer Dim stod As String Dim etod As String pday = 8 pnight = 12 shour = Hour(st) smin = Minute(st) + shour * 60 If (shour >= 8 & shour < 20) Then stod = "day" Else stod = "night" End If ehour = Hour(en) emin = Minute(en) + ehour * 60 If (ehour >= 8 & ehour < 20) Then.................
View Replies!
View Related
Timesheets  Time Arithmetic
I have a timesheet in excel which details the hours worked per person. It is worked out by have time started in one cell, and time finished in the next cell. (24 hour clock). The timesheet is for a night club, so people start late, and finish early. Therfore, in the total column I have the following formula... =IF(D5="",0,IF(D5
View Replies!
View Related
Importing Time/date Data Into Timesheets
I have been tasked with creating a workbook that will take a time log file (excel) from an electronic time clock and import the dates/time/employee into individual timesheets. The timesheet layout has already been provided to me and cannot change. Does anyone know how to write formulas that would gather the date range/time inout/employee information from one worksheet and automatically import it into the appropriate employee time sheet for the week selected?
View Replies!
View Related
Multiple Timesheets, Auto Fill From Data Sheet
I have been tasked with creating a workbook that will take a time log file (excel) from an electronic time clock and import the dates/times into individual employee timesheets. The timesheet layout has already been provided to me and cannot change. I’m really struggling here and would appreciate any help. Please reference the attached workbook for the issues described below: 1) Sheet "1_attlog" is the log that is imported from the electronic time clock. 2) Sheet "Sorting & Filtering" is what I have developed so far that manipulates the data from sheet "1_attlog" into something that can be easily understood, it also matches employee #'s to a name, and allows for date ranges to be set that copies over to the individual timesheets. Please do not change any formulas on this page if possible. 3) Sheets "#2 Dan through #10 Blank" are all individual employee timesheets that I need to import the data from "Sorting & Filtering" and populate the "Time In" (column D) and "Time Out" (column G) based on matching the data from "Sorting & Filtering" sheet, "Time Clcok ID" (column D) "Date" (column E), "Time In" (column F), "Time Out" (column G), "Emp Name" (column I). The dates on the individual timesheets have been linked to the "Date Range" (cels F2 & F3) on the "Sorting & Filtering" sheet, please keep this link intact. 4) The individual timesheets already have formulas inplace to calculate the hours worked after the "Time In" and "Time Out" data has been imported.
View Replies!
View Related
Solve Between 24 And 48 Hours OR Less Than 24 Hours OR Greater Than 48 Hours
In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time. Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.
View Replies!
View Related
Formula For Kilowatt Hours
I am creating a comparison sheet for kilowatt hours used, in D29 I have the actual KWH used. What I need is a formula that will give me the cost of the 1st 400 KWH at $0.08570 per KWH in BV7, the 2nd 400 KWH at $0.12175 per KWH in BW7, and the cost of the remaining KWH at $0.14427 per KWH in BX7. Now be advised that some times the KWH may totol 653 for the month and sometimes 1950 for the month.
View Replies!
View Related
Formula To Calculate Hours
im doing an excel spreadsheet. on 1st sheet i have a summary page for some clients, on sheet too i have worked out and done the formula so that it calculats values i put it. i want to take this total value i have worked out (which is a variable will change from day to day) and move this value to sheet one. from here i want the number that i show in the summary page to deduct from a fixed total in the cell nxt to it. and ideas ive tried several options coming close but no ciga
View Replies!
View Related
If AND Formula Using Hours And Minutes  Query
I am trying to write a formula that will take the value of cell J9 which is in hours and minutes format (hh:mm) and return a value in £. ie. if J9 5hours and 12hours £15.00 I used this =IF((J9)5,(J9)24,(J9)>24),15,))) returns #Value! It won't work as it won't accept the hours and minutes format in J9. If I maually enter a value in J9 like 7.00 it returns the correct £12.00
View Replies!
View Related
Formula To Calculate Hours Worked
I have this spreadsheet and in it the time is changed from military time to regular and then I use a formula to calculate hours worked. On some of these the total is off by one minute. Does anyone know how to fix this? I don't know how to paste the spreadsheet so you can see formulas,
View Replies!
View Related
Formula Which Will Work Out 48 Permutations
I need to work out a formula which will work out 48 permutations. There are three headings: Impact, Probability, Risk Category: ImpactProbabilityRisk Category VHM = 1 VHH = 1 VHVH = 1 HH = 1 HVH = 1 VHL = 2 HM = 2 MH = 2 MVH = 2 HL = 3 LVH = 3 MM = 4 LH = 4 ML = 5 LL = 5 LM = 5 The formula I need is: if Impact is "L" and probability is "M", then return answer = "5". The thing is  there are 48 possible answers  and im not sure what formula you could type into 1 cell  which would answer this.
View Replies!
View Related
Formula To Calc Total Hours For Each Instance
I want to find out how many hours, in total, relate to "consolidation 1, 2, 3; Lease Renewal 1,2 3 etc. What formula can I use. I am working on a "sumif" but it's not working out for me. Hours 1 Consolidation 4.75 3 External Sublea 3 Consolidation 2 3 Special Project 2 External Sublea 1 Lease Renewal w 1 New Lease w/o T 4 1 New Lease w/o T 1 1 External Sublea 3 Lease Renewal w 1 2 Lease Renewal w .5
View Replies!
View Related
Formula To Total Hours And Minutes In A Column
I am using a formula such as =Text(A5E5,"H:MM) to get the difference in clockin time and clockout time on a daily basis (MondaySaturday). I want to add the results as a total for the week. I am not sure what formula to use to get that result. I prefer not to use decimals unless I have to. Also, the above formula does not work when the time goes past 12 midnight.
View Replies!
View Related
Formula To Add Up Hours That Were Attended For Various Individuals
I'm trying to figure out how to write a formula to add up hours that were attended for various individuals. My data is coming in from scanning their barcodes, so I'd rather not manually add their hours. I have these columns: Date, Time, First, Last, Member ID, CEU. My hope is that I can take the Member ID and write a formula that adds up the CEU column. This issue I'm having is how to deal with the variables of someone only attending two sessions, while another attends four? I'm not sure how to do this so I'm seeing if anyone knows if this can this be done?
View Replies!
View Related
Format Total Hours To Days, Hours & Minutes
1) The output of an excel duration is : 22.00:8.00:25.00 ( day:hour:minutes )  excel cannot average and work with this number format 2) resolution  =(LEFT(L2,4))+MID(L2, FIND(":",L2)+1,4)/24+MID(L2, FIND(":",L2,7)+1,4)/1440 as an array and Custom Format the cell as [h]:mm  works perfectly. Q: to be conistent, the initial reporting is dd:hh:mm and then I convert to hh:mm so that excel can process the data. How can I convert from hh:mm to dd:hh:mm so that the excel report can be consistent in presenting the data to senior management? example attached.
View Replies!
View Related
Formula To Calculate How Many Hours Minus 30 Mins For Lunch
If a person does a full day I want the formula to calculate how many hours minus 30 mins for lunch i.e. 7:30  4:00 = 8 hours. I have done this and it works fine, the problem comes when a person does a half day i.e. 7:30  11:30, this will read 4.5 hours. If this is the case I don't want the 30mins deducted so it should be 4.0 hours worked. I have high lighted a column in the work sheet (yellow) whereby the user puts an 'x' in the appropriate cell if they are working a half day. The probem is I cannot incorperate the two formulas in one cell (the Actual hours cell) I have the one formula in the actual hours cell and the other bolted on the end thus giving two different readings.
View Replies!
View Related
