Calculating Time And A Half And Double Time (After 40 Hours)
I'm battling to come up with the formula to calculate time and a half and double time. My boss wants me to show the overtime worked AFTER 40 hours has been worked. I have attached a copy of my spreadsheet.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculating Hours With Midnight As A Start Time
I’m working on a timesheet and I need to separate the hours worked that are before 6 am from all the others. For example if someone works 1:00 am to 8:00 am I need a cell to populated with 5 representing the hours worked before 6 am. The formula below works fine except when the start time is 12:00 am. I am also having trouble if the start time is before midnight like in a 11pm to 5 am shift. =(IF(AND(S3<=$AG$97,S3>=$AG$73),(($AG$97S3))*24,0)) S3 is the start time AG97 is 6:00 am AG73 is 12:00 am
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
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
Calculating Time Using Microsoft 2003. Carrying Over Hours Only?
I am using Microsoft Excel 2003. My question is about calculating time. Let's say my answer, after calculating time, is 2 hours and 1 minute (2:01) and that answer is placed in cell A1. How would I be able to have only the 2 hours (2:00) carry over to cell A2 without the minutes showing? I have tried =MOD(A1,24/24) and =TIME(0,HOUR(A1),0). I have also tried to right click the cell, went to Format Cell, went to the Numbers tab and tried the different options in the Time and Customs category, but I can't seem to get 2:01 to appear as 2:00 only.
View Replies!
View Related
Calculating Hours Worked Based On Elapsed Time Over 24H
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 (A2A1)*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 Replies!
View Related
Calculating Date & Time Difference: Result In Hours Only
Instead of calculating a time difference manually I want to have a formula do it for me. What I want to achieve goes as followed: 08/06/2006 04:33 12/06/2006 01:05 Time difference is 92:32 [hh:mm] another example: 09/06/2006 12:42 12/06/2006 11:35 Time difference is 70:53 [hh:mm] So instead of getting 92:32 and 70:53 by calculating it myself I would like to have a formula do it for me. Otherwise I'll have to invest a lot of time to get the information I need.
View Replies!
View Related
Calculating Time Owed Based On Time Worked
I know the title is a bit vague, but I cant think how else to word it! I have a sheet (attached) which works out hours worked, and if the amount is under a specified target, it counts how much time is owed. The problem occurs when someone works more hours than the specified target. I guess I need an IF formula of some kind, to say if the figure is over the target, to put zero in the hours owed column.
View Replies!
View Related
"Time" Function To Find Time&date  ?hours
I m Trying to add some features to it and run into an issue. Here are the fields we are working with! A1= 13:30:00 17/12/2008 (in other words 1:30pm on date given)  Need to know proper cell number format!! B1= 23:00:00 (this represents 23 hours)  format for cell is [h]:mm:ss C1= Unknown function (This field is my problem!!) Answer here SHOULD be "14:30:00 16/12/2008" I need a function for C1 (using A1 and B1) that will give me the time and date 23 hours earlier or however many hours:mins:secs is specified in B1! Also need to know what number format to use for both A1 and C1
View Replies!
View Related
24 Hour Double Time
I have a timesheet which for certain days of the year calculates doubletime. What i would like to do is have a formula that gives double time for a 24 hour period. I have a formula setup with a start time and end time for my double time, but it wont stop at midnight.
View Replies!
View Related
Calculating Fullwidth Halfmax (fwhm)
calculate the fullwidth halfmax of a peak in data? My chart (gray value vs. pixels) essentially looks like a letter M. What I've tried is finding the max of each peak, dividing that by two, and then using vlookup to find the corresponding pixel values on each side of the peak. But, since vlookup does not work for descending values, I cannot find the halfmax value on the right side of each peak.
View Replies!
View Related
Double Click In Cell To Show Current Date And Time
I would like to be able to track progress of various tasks/occurences by allowing users of a spreadsheet to just click in the appropriate column and in doing so, it would fill in the current time and date. I.E. Column headings would read, "Item Sent to Review", "Item Received From Review", "Item Sent to Specialist", etc. and I want users to be able to double click in a given column to populate with current date/time information,
View Replies!
View Related
Display End Time Automatically Upon Entering Start Time And Time Usage
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View Replies!
View Related
Calculating Using Time
Firstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6AC6 divided by the frames, every time I try it I get a messed up answer.
View Replies!
View Related
Calculating Time In
=INDEX({"$5.00","$3.00","You Were Late"},MATCH(L17,{7:00am,7:05am,7:06am},1)) i have this formula in excel...this formula sanrv1f posted to help with another question (values changed) but i thought i would work with what i wanted but i get a (Value) error so what im trying to do is base on the system time is if the person typed in if they were early 6:45am to 7am they get Extra $5 on top of the $5 they get for being on time. 6:55am gets $5 7:00am they get $5.00 for being on time if they type 7:01am to 7:05am they get $3.00 if they type 7:06 to 7:10am would return You Were Late after 7:10am would return No Pay so Ex D4 *answers in E4 * 7am would get $5 7:03am am would get $3 7:07am would get You Were Late 7:11am Would get No Pay if they were on time 5 times (ie 5days in a row) they would get a bonus of $20 i tried factoring this in could not do it :/
View Replies!
View Related
Calculating Time
basically I need an excel sheet to calculate the period of time between cells. For instance, in A1 I write: 18Feb10 In A2 I write: 13:00 Unfortunately I cannot write 18Feb09 13:00 in just one cell because it doesn't work. Now, in B1 I write 19Feb10 and in B2 I write 15:14 Now we know that 26 hours and 14 minutes have elapsed between these two times. So if I want this to automatically be calculated in B3, what do I do? Also, how do I apply the formula to all cells in column 3 so all I have to type in is the date (in column 1) and the time (in column 2)?
View Replies!
View Related
Calculating Value, Based On Time
I am trying to write what I believe is a fairly simple set of calculations to determine nonqualified stock option values. Before this digresses into a discussion of the merits of BlackSholes methodology, I must tell you that this is a "101" type sheet. I'm not an economist. The deal is that my sister (for whom I'm constructing this worksheet), gets employer stock options which vest at different intervals. In the past year, she's gotten some options that vest in two, four, and five year increments. I'm using Excel 2002, with the analysis toolpak, and stock pricing addin from MS. Have today's date, date of option grant, and can easily compute time (in years) until options are fully vested. The trick is that options with a 2year vesting period are 50% excerciseable in one year, 100% in two. Fouryear options are excerciseable 25% per year for 4 years; 5year are 20% for 5 years, etc. I'm trying to write a single datedriven formula to take each grant (individually), and divide it by the appropriate divisor (based on the current date), to figure out the current value. I can't seem to quite figure it out how to write a formula which can handle: Shares Strike Price Date of Grant Years Until Vested Value 100 26.57 1/4/2003 5 245 45.67 2/6/2001 4 Value should equal = (if today's price is grater than Strike Price) (Current Price * (Shares * (% vested which is based on "date of grant" + "years until vested")) )  (Strike Price * Shares * (% vested which is based on "date of grant" + "years until vested") ) how the math to make the "% vested multiplier" work...
View Replies!
View Related
Calculating Time Blocks
I am trying to calculate the amount of time in blocks, using a predetermined spread. For example: AM block is anytime between 06001230 PM block is anytime between 12301830 Evening block is anytime between 18300000 and the Overnight block is anytime between 00000600 Sounds easy enough, yeah? Let me paint an example of how I want it to calculate though: If an employee starts at 0600 and finishes at 1400, then the AM total is 6.5 and the PM total is 1.5. If an employee starts at 1100 and finishes at 1900, then the AM total is 1.5, the PM is 6 and the Evening is 0.5. If an employee starts at 2200 and finishes at 0800, then all of the 10 hours must report to the Overnight total, even though some of them fall within the Evening and AM blocks. I just can't seem to get each block to show the accurate number of hours...and to bring in the rules that I require.
View Replies!
View Related
Calculating Time Variance
I'm trying to calculate the variance between planned date & time of arrival vs actual date & time of arrival. I attach the workbook as am a bit useless at explaining myself.... What I've done is in H14 subtract the actual date of arrival (F14) from planned date of arrival (C14). This result is the only way I could think of dealing with crossing over midnight. As a result I14 should subtract the actual time of arrival (E14) from planned time of arrival (B14): =SUM(E14B14,H14) This method works well when the arrival was later than expected but doesn't work if the arrival was sooner than expected.
View Replies!
View Related
Long Calculating Time
I managed to put together an array formula to calculate the last date that a rep made a sale. It checks two other tabs in the workbook to find the date, and if none is found, it leaves the cell empty. {=IF(MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 1107 to 408'!B:B=B48,'Daily Compliance 1107 to 408'!A:A))=0,"",MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 1107 to 408'!B:B=B48,'Daily Compliance 1107 to 408'!A:A)))} But it takes sometimes up to 45 minutes to make the calculations. Is there possibly a way to simplify it so that it calculates faster, with the same results?
View Replies!
View Related
Calculating Task Time
I have a sheet to calculate Time Elapsed based on StartDT & EndDt taking into consideration the actual working hours in any working day and the function exclude the weekend. I am not a guru in excel but i found this formula in this website . =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDTStartDT),2), (24*(DayEndDayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT1,HolidayList),0)+ INT(24*(((EndDTINT(EndDT)) (StartDTINT(StartDT)))+(DayEndDayStart))/(24*(DayEndDayStart))))+ MOD(ROUND(((24*(EndDTINT(EndDT)))24*DayStart)+ (24*DayEnd(24*(StartDTINT(StartDT)))),2), ROUND((24*(DayEndDayStart)),2)))))) the formula works perfectly except for the fact that in our part of the world we have "Friday & Saturday" as the weekend days instead of Saturday and Sunday. is there a way to get this formula or any similar formula to take different weekend days and do exactly what i need?
View Replies!
View Related
Calculating Turn Around Time ..
I've browsed the net searching for a solution to my problem and found one solution altough not entirely what i was after, but it was very close. It was provided on another forum, of which i'm not a member. [url] ... I have a TAT target of <= 2hours, however this is complicated by weekends, holidays and jobs received after working hours and on weekends. I've attached the file, which contains detailed information about my problem.
View Replies!
View Related
Making Time Roll Over After 24 Hours
I'm creating a calculator to determine the distance & travel time on a xy grid for a game. I'll share what I have so far to display the big picture. Here’s what I’ve accomplished so far: #1) Cells A1 & B1 are dynamic, they are frequently changed so distance and time may be displayed. Cells A2 & B2 do not change. #2) I use the following formula to determine the distance between cells, displayed in C2: =SQRT((A2$A$1)^2+(B2$B$1)^2) #3) Next I multiply C2 by a number so that distance is measured correctly per unit type for this game. (I hide this column). Therefore, cell D3 has the following formula: =C2*20 #4) Now my problem, cell E3  Correctly displaying time. I have the following formula in E3: =TIME(0,D2,0) This works great!! That is until the time goes beyond 24 hours, then it rolls over. I've tried right clicking and setting the custom format of E3 to [hh]:mm, it still rolls over. I’ve also tried setting formatting to d:[hh]:mm, if I do this then the output time displayed is incorrect. Ideally I would love a simple fix so that the time will roll over and show days. (I would like a display similar to d:hh:mm). I’ve done a fair bit of research online and either I'm missing something simple or this is more complex than I thought. And not to make this any more confusing, but I would love whatever I accomplish here to be useable in compatibility mode ...
View Replies!
View Related
Time Mask For Thousands Of Hours
Basically I want to be able to type a number and have a colon inserted two spaces to the left. So 123 would become 1:23, 1234 would be 12:34 and 12345 would be 123:45. I'm working with anywhere from one to several thousand hours in a spreadsheet. I'd like to be able to use a time mask like this:
View Replies!
View Related
Conditional Formatting Using Hours/time
I'm trying to use conditional formatting to highlight phone calls that came in between certain hours. The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames. Example: If the call came in between 22:00 and 23:59 color is light green. If the call came in between 23:59 and 08:00 the color is yellow. Example spreadsheet is attached.
View Replies!
View Related
Calculating Turn Around Time Between Two Times
i want to calculate the TAT between two times. the TAT target is <= 2 hours. i used the following formula a1 has 3/13/2009 (received date) b1 has 7:08 AM (received time) c1 has 3/13/2009 (completed date) d1 has 9:08 AM (completed time) e1 has TAT formula : '=IF((D1B1)*1440<=120,"Met TAT","Not Met TAT") however this formula does not work in the following conditions. In these conditions, it is considered that TAT is met. 1. When the difference in time is <=2 hours .... for TAT calculation, on working days and working hours are taken into consideration. To illustrate. Day begins : 8:00 AM Day ends : 4:00 PM If job is received at 3:30 PM and completed the next working day by 9:30 AM, then it is considered TAT is met. calculation = 4:00 PM  3:30 PM = half hour + next day's 9:30 AM  8:00 AM = 1.5 hours, therefore, total working hours used to complete the job is within the agreed TAT. If job is received and completed on non working days and during non working hours, it is considered TAT met. If job is received almost at the end of the day, say, 3.30 PM and job is completed at 8:00 PM same day, then it is considered TAT met, rationale, only half an hour of working hours used to complete the job.
View Replies!
View Related
Calculating Time Across A 24 Hour Period
Calculate certain time increments for various workshifts. I have a start time,finish time and increments of time across the spectrum of 24 hours. There are also multiple start time across the 24 hour period with some start times begining on one day and ending on the next day. Example In B5 Startime is 22:00 In C5 Finishtime is 06:30 In I3 increment begins at 00:00 In I4 increment ends at 00:30 The employee working the shift from 22:00  06:30 would fall into the time increment of 00:00  00:30 where another employee working a different shift (08:30  17:00) would not. I'm looking for a formula that would return a 1 in a cell if the employee fell into the 00:00  00:30 time increment and a 0 in a cell in the employee did not fall into the time increment.
View Replies!
View Related
Calculating Date AND Time Differences
Within 4 columns, I have a series of: Start Dates / Start Times / End Dates / End Times I need to create a formula which will give me the sum total of the difference between the data sets. For example, the time difference between 8am on the 20th March and 1pm on the 21st March is 29 hours OR 1 day and 5 hours. I need a formula which will calculate this for me.
View Replies!
View Related
How Do I Reduce The Calculating Time In A Workbook
I have been building a spread sheet data base which I think is pretty simple. It now has quite a few formula's and is taking time to calculate when I add data. I have tried to simplify some formula's and get rid of complex one's that can be replaced with simple ones... Is there any advice or suggestions for reducing the calculating time..?
View Replies!
View Related
Calculating Time, Timesheet Calculation
I am working on a project involving calculating time. It is a timesheet calculation. I was able to design the following layout: .....A............B..........C..........D.......E.....F 1....Date.........Time IN....Time OUT...Hours... Total 2....01/01/07.....1830.......1930.......01:00...01:00 3....01/02/07.....1930.......2330.......04:00...05:00 4....01/03/07......830.......1900.......10:30...15:30 5 Column A is formatted for DATE. Columns B and C are GENERAL. Columns D and E are DATE format customized as '[hh]:mm' The formula to calculate the time difference between the numbers in column B and C is located in column D. It is as follows: =IF(C4<1000,TIMEVALUE(LEFT(C4,1)&":"&RIGHT(C4,2)),TIMEVALUE(LEFT(C4,2)&":"&RIGHT(C4,2)))IF(B4<1000,TIMEVALUE(LEFT(B4,1)&":"&RIGHT(B4,2)),TIMEVALUE(LEFT(B4,2)&":"&RIGHT(B4,2)))..................
View Replies!
View Related
Convert Time Decimals To Hours Mins
I am creating a very quick holiday excel sheet where people can fill in the hours they want on which days. I have a summery sheet too. So if someone wants to take a full day which is 7.4 hours. How would this be represented as Hours and minutes?
View Replies!
View Related
Calculate Date/time Format Over 24 Hours
I need to calculate the difference between two cells that are formated like this: 11/10/2006 13:00. Simple subtract only works when the date hasn't changed (the subtract formula seems to focus on the time and ignore the date). I found a formula that will work, but the results are in regular numbers, I need the hh:mm format and if I change the format of the result column, the time displayed is wrong. The formula is=((B2A2)*1440)/60) If my result is 122.6 in regular numbers, when I convert to hh:mm I get 14:00. Is there a better way to do this? Also, is there a way to eliminate weekend & holiday hours from the calculation? This is not as important as getting the results to calculate correctly in the hh:mm format.
View Replies!
View Related
Calculate Hours Worked After Specific Time
i have a timesheet that we are trying to use. the problem is the column that says shift diff. if an employee works after 6:30pm for 1and 1/2hr, he is entitled to shift hours. shift hours is between 6m and 8am. As long as he works after 6.30pm but works for at least one and a half hour, he will get the shift. if work, 9am to 7:30pm, and have break between 23pm, should have 1.5hrs shift and 9.5hrs total if work, 7:45pm9:45pm, and have break between 8:309pm, total hrs work is 1.5 and shift hrs s/b 1.5hrs if work 3pm to 12am and have break between 78pm, total hrs work is 8 and shift hrs s/b 5hrs
View Replies!
View Related
Time/date Tracking Only During Business Hours
i work dispatch at a company and we're trying to track how long it takes for a technician to arrive onsite. my worksheet currently includes the date of callin (E1) , time of callin (F1), Service Date (G1), Service Time (H1), among many other field. the easy solution is to use the formula (G1+H1)(E1+F1), and use the answer as the total amount of time, but the problem is if a customer call in at 3:00 PM and we service them at 9:00 AM the following day, it looks like it took 18 hours to arrive onsite, when in reality, since we close at 5:00 PM and open at 9:00 AM, we only took 2 hours to arrive onsite. is there any formula i can write to account for nonbusiness hours and weekends, or is this a bit above what i should expect from excel?
View Replies!
View Related
Calculate Time Difference Greater That 24 Hours
I have an excel spreadsheet where you enter the start time and end time for job function. Since some of the times cross midnight, I use the formula J3=IF(I3>H3,I3H3,1+I3H3) where I is the end time and H is the start time (format hh:mm). This part works fine, however when I sum column J and change my format to Time 37:38:00 (since it is over 24 hrs), it returns a large number of 2234:48:39 which should be closer to 223:00:00.
View Replies!
View Related
Adding X Hours To Date & Time
I have a problem concatenating time in excel if it is of Date + Time format.. What formula do I use to just add the time by, say, 3 hours? I'll need to use, say cell B2, which will add 3 hours to it.. What formula do I use?
View Replies!
View Related
