Date/Time Calculation For WeekEnds/After Business Hours
What calculation would I enter in a results cell if I wanted to find the delta between 2 times in date format that repersent just the business hours of 8am-5pm, therefore excluding after hours and weekends.
eg.
Date 1 Date 2 Result Time
23/07/07 8:00 24/07/07 14:55 15:55
So far I can't get the caluclation that will compensate for the after hours and week ends.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 on-site. my worksheet currently includes the date of call-in (E1) , time of call-in (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 on-site, when in reality, since we close at 5:00 PM and open at 9:00 AM, we only took 2 hours to arrive on-site. is there any formula i can write to account for non-business hours and weekends, or is this a bit above what i should expect from excel?
View Replies!
View Related
Calculation Of Business Hours
I am using the following formula to calculate business hours. =(NETWORKDAYS(R9,T9)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(R9,T9),MEDIAN(MOD(T9,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(R9,T9)*MOD(R9,1),"17:00","08:00") The business hours considered here is 8AM - 5PM, Start time in R9 and End time in T9. Now the problem is its calculating the correct value when the days are same, for e.g., Condition 1 When I am giving "31 March 2009 15:00:00" as start time (R9) and "31 March 2009 23:00:00" in end time (T9), I am getting the correct value. i.e, "2:00:00" Condition 2 While giving "31 March 2009 16:00:00" as start time and "01 April 2009 09:00:00" as end time I am getting a value of "1:00:00", actually the value should be "3:00:00".
View Replies!
View Related
CALCULATION TAKING BUSINESS HOURS INTO CONSIDERATION
I need to do an hour calculation on two cells which have dates and times in both. the first cell is a call that we get from a customer and the second is the date and time in which that call is closed by us...meaning that call is complete. I need to calculate how much time in hours did it take us to complete that call for the customer. I need this calculation to respect our business hours of Monday to Friday 8am-5pm and closed on Saturdays and Sundays. here are some examples. from - 2/12/2004 13:00 (thursday) to - 2/13/2004 9:00 (friday) answer should be 5 hours from - 2/13/2004 14:00 (friday) to - 2/16/2004 10:00 (monday) answer should be 5 hours
View Replies!
View Related
Calculate Elapsed Time Based On Business Hours
I've being trying t calculate an excel formula to work out a order turnaround time based on opening hours. Example: The store is open from 8 am to 6pm monday to friday, I need to get a formula to work out how long it took from the time the order was logged until it was completed, during the course of the working day - this works fine but if the order was received on 06/12/07 at 5:20 pm then was finally completed by 7/12/07 at 11:55 the next day I need to figure out how to take into consideration of non working hours during that time. (time from 6pm to 8 am next day example 2 - if order was sent on the 06/12/07 at 01:30pm and completed at 06/12/07 4:30 then I know its taken 3 hours to complete example 3 - if the order was sent on the 06/12/07 at 5pm and completed next day at 07/12/07 at 10am - then I know it took 3 hours to complete
View Replies!
View Related
Skip Weekends Adding Time To Date & Time
I need a formula to add just the time to ' date and time', ignoring weekends. eg: Fri 24-Aug-07 10:52 is the date and time 28:48:00 is the time If I add the time to 'date and time', result is coming as Sat 25-Aug-07 15:40 But it should come as Mon 27-Aug-07 15:40 (hence ignoring weekend)
View Replies!
View Related
Calculate Business Hour Downtime Excluding Weekends
I am trying to calculate downtime for a Service Level Agreement. The data that I have is the start date/time and the resolved date/time for an incident. The data are in the format - 1/1/2008 03:32 AM. The incidents may occur at any time but downtime is calculated only business hours and excludes weekends. I may be required to exclude holidays but that is not a hard requirement right now. What I must be able to specify are the working hours. As example(s) - if an incident is generated on 1/2/2008 4:00 PM and resolved at 1/3/2008 11:00 AM then the downtime is 4 hours. - if an incident is generated on 1/12/2008 4:00 PM (which is a Sat) and resolved at 1/14/2008 11:00 AM (which is Monday) then the downtime is 3 hours. The above assumes working hours are 8:00 AM through 5:00 PM. I have tried using the NETWORKDAYS and WORKDAY functions with little success.
View Replies!
View Related
Time Calculation: Calculate How Many Hours Have Elapsed Between To Entries
I am trying to build a spreadsheet to calculate how many hours have elapsed between to entries; start time (H10) e.g. 9:15 AM and end time (I10) e.g. 12:15 PM. The formula that I am using in the calculation cell field (J10) is (I10-H10+(I10<H10))*24. This formula works great till I wish to include in an IF statement. What I would like is if the total hours calculated with the formula (I10-H10+(I10<H10))*24 is less than 4, return 4 (hours) otherwise the value. As well if there is no start time nor end time entered then return zero.
View Replies!
View Related
Adding Time To A Date Excluding Weekends And Holidays
I've been reading up on Excel's date and time functions and can't really figure out the best way of doing this. I have a total amount of time that a machine should take to finish a task. I'd like to enter a date and time into a cell (Start Date) and have another cell return the date and time that the machine should be done with the task excluding weekends, and holidays (End Date). This would be based on a certain number of "working hours" (hours in the workday minus break periods) that would be calculated in another cell.
View Replies!
View Related
Calculating Business Hours
I use this formula at work to calculate business hours from Mon-Fri: =IF(OR(L10="",L11=""),"",(NETWORKDAYS(L10,L11,$N$2:$N$23)-1)*($Q$3-$Q$2)+IF(OR(ISNUMBER(MATCH(INT(L11),$N$2:$N$23,0)),WEEKDAY(L11,2)>5),$Q$3,MEDIAN(MOD(L11,1),$Q$3,$Q$2))-IF(OR(ISNUMBER(MATCH(INT(L10),$N$2:$N$23,0)),WEEKDAY(L10,2)>5),$Q$2,MEDIAN(MOD(L10,1),$Q$3,$Q$2))) where Q3= business start time 8.30am where Q2= business end time 5.30pm thus the difference between 18-Apr-08 16:30 and 21-Apr-08 13:30 is 6 hours. I now need to adapt this formula for another Department that also works on Saturday from 8.30am to 5.30pm.
View Replies!
View Related
Calculate Hours Worked, Minus Weekends
to calculate how long a ticket is open in our system before being resolved. I don't want to count weekends, and if the ticket is 'suspended', I don't want to count that either. There is also the factor that the ticket 'un-suspend' date may be later than the ticket 'closed' date. Which is the bit that's throwing me. So, I have the following fields Ticket Open, Ticket Closed, Ticket Suspended Date, Ticket Unsuspended Date A sample ticket might be (using above fields) 02/11/09 09/11/09 04/11/09 30/11/09 That 'should' equal two days (16 hours) as the Unsuspend date falls after the close date so it was suspended from the 4th until closure. Now I want to know, in hours (8 hour day) how long that ticket took to resolve (i.e close), remembering you can't count the time it was suspended, or any time that fell over a weekend. Also not all tickets are suspended.
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
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=((B2-A2)*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
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
Calculate Number Of Hours Between Two Date/time Combinations
I am trying to find a way to calculate the number of hours between date/times found in separate rows. The attached data set will help to envision what I am talking about. For each couple of rows, I need to find a way to calculate the number of hours elapsed from row 1 to row 2. In the first example, to calculate the number of hours between 12/2/2009 8:56:51 and 12/4/2009 6:35:27.
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 8am-5pm. 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
Check Date & Time For Missing Hours
I have a column A with some dates at this format "dd-mm-yyyy hh-mm". In this list, some hours are missing, and I don't know which ones. In order to find the missing hours, I created an additional column B, displaying all the hours, this way: B1=01-01-2006 00:00:00 o B1=A1 And then : B2=B1+"01:00:00" Then, in column C, I compare both columns A and B: C1=IF(Value(B1)=Value(B2),ok,missing) As a result, I first get some "ok", and then more and more "missing" statements, even though the dates look the same. I checked the numerical values of my dates. At the first "missing" statement, I saw that there is a slight difference in the numerical values, at small decimals... Can you tell me how to define my date /time in a good way, in order to avoid this kind of problems, please? (I could take the int part of the numbers, but this could lead to further errors, so I prefer to define my dates in the right way, from the beginning).
View Replies!
View Related
Add X Hours By Condition To Date & Time
Column 1: Date Call is received by helpdesk Column 2: Time Call is received by helpdesk Column 3: Drop-down list indicating Priority of call (High,Medium,Low) In Column 4 I want to enter a formula that adds working hours only to the date and time entered in Columns 1 & 2. The time added will vary depending on what is entered in Column 3 (e.g.: if priority is High add 1 hour, Medium add 2 hours, Low add 3 hours). The working hours i need to adhere to are 09:00-17:00, Monday-Friday.
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
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
Subtract Date/time And Get Elapses Days, Hours, Minutes, Etc
I want a forumal to do in excel. Assuming that a workday is from 8:30am to 5:00pm. (also need to not calcualte Saturday and Sunday but this example doesn't show it). Wednesday, 7/18/07 at 4:00pm (minus) Friday, 7/20/07 at 9:00am (should equal) 10 hours or 1 day, 1 hour and 30 minutes. Basically what I want to do it determine how much work time has elapsed from the time a message was left on voicemail to the time that someone picks that message up. We only want to assume that a message can be retrieved duruing business hours but a message can be left at anytime!
View Replies!
View Related
Formula For Date And Time Difference Using 8am-5pm 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(B1-A1, "d:hh:mm") And how do I account for weekends?
View Replies!
View Related
Time Difference Ignoring Weekends
provide me a formula to calculate the time difference in format [h]:mm:ss The formula should ignore saturday and sunday. Eg: Fri 31-Aug-07 09:49 Mon 03-Sep-07 19:13 If I take the exact time difference, it is 81:24:37 But it should actually be 33:24:37, excluding 48 hours of weekend.
View Replies!
View Related
Flying-Hours Calculation
One of my administrative duties is to keep a record of all of the flying-hours completed by a group of twenty pilots. I've constructed a spreadsheet and entered all of their flying records into it. At the head of each column I have the date, aircraft type, registration number, pilot name, co-pilot name, other crew name, day flying, night flying, solo, dual, total captain hours, etc. Whilst that I've completed the easy part of this project and that I can transfer each individual pilots flying-hours into his own seperate logbook (by filtering and copy/pasting into another worksheet), there are three other reports that I'm required to provide: 1. To be able to list the number of flying-hours completed during the previous 7 days (for each individual pilot). 2. To be able to list the number of flying-hours completed during the previous 30 days (for each individual pilot). 3. To be able to list the number of flying-hours completed during the previous 90 days (for each individual pilot).
View Replies!
View Related
Hours Worked Calculation ....
I am trying to create a spreadsheet that auto calculates my emp. time. However I do not want to use military time. I can get it to work by =a2-a1 but only if it is 8.5 and 17.5. Any ideas how I can do clock in 8:30 clock out 4:30 = 8 hours?
View Replies!
View Related
Shift Hours Calculation
I have enclosed a sheet with the dilemma i currently face. Ive tried multiple variations on a solution none of which have been 100% accurate. Basically the work day is split into 3 shifts : Days ( 06:00 - 14:00 ) Afters (14:00 - 22:00 ) Nights ( 22:00 - 06:00 ) I have a report which tells me the total time the colleague will be getting paid for and there clock in and out times. I need to determine which shift bracket there hours fall into based on the time bands. Ie : David worked 8 hours , started at 10:00 finished at 18:10 , so thats 4 hours recorded in days and 4 in afters since he worked across both shifts. the 10 minutes is not being paid so it doesn't need to be recorded. the sheet should explain things better.
View Replies!
View Related
Difference Between Dates & Times In Business Days With Time Constraints
I need to create a spreadsheet that will calculate the difference between two dates and show the result in days. The complicated part is that weekends (Sat and Sun) need to be ignored and any time after 4:30 pm needs to be treated as the next business day. I have been trying to look at similar questions in forum and use the WORKDAYS function with little success. Example: A1- 14/08/09 4:40 p.m. B1- 17/08/09 9:10 a.m. I need the answer (B1-A1) to show up as 0. But if the time in A1 was changed to 4:20 p.m. than the answer should be 1 day.
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
Adding Business Days To A Date Formula
I am looking for a formula that looks at a date and could add business days to it. for example: If the date in a field is Monday, 15-dec-08 and my formula is to add 5 Business days (mon-fri) to it not including that date. The desired result would be Monday, 22-dec-08 but my formula gives me 20-dec-08
View Replies!
View Related
Date Difference Without Weekends
what formula I can use to calculate the difference between two dates while not including weekends. For example in cell A1 I have 27-Feb-07 and in B1 I have 05-Mar-07 ; it is possible to to calculate the difference between these by not including weekends. The weekends I am refering to are Saturday and Sunday.
View Replies!
View Related
Calculate A 24 Hour Cycle Time Which Excludes Weekends And Public Holidays
I would like to find out if a job took more than 24 hour cycle time (eg. 6:00 am to 5:59:59 AM next day). data: A1 = received date & time (format "m/d/yyyy h:mm") B1 = completed date & time (format "m/d/yyyy h:mm") my formula is '=if(B1-A1>"24:00"+0,"Yes","No"). The problem with the formula is that it doesnt exclude weekends nor public holidays. I couldnt formulate a solution using networkdays function......
View Replies!
View Related
Determine A Specific Date Based On A Number Of Business Days?
I'm documenting the steps of a process with each step accounting for a specific amount of business days. How do I determine the specific business day based on the number of days it takes to complete a process? For example: Start date then 45 business days from that date the next step begins...then 3 business days from that date the following step begins and so on.
View Replies!
View Related
Exclude Weekends In Date Counting
I have been trying to count dates in my spreadsheet. The dates are supposed to go back in time (what they do). But I want them to jump over the weekends' dates instead of landing on them. What I have is Start Date, Req'd Qty, Days it takes to make, Date it is required to start production (which I have a problem with). A 1 26-Nov [Thursday] 2 1500 3 5 4 'Problem' I used for this cell that formula: =if(A2>0,A1-A3,"") , as I don't want it to show anything if there is no requirements. It comes back with 21-Nov which is Saturday when I want it to come back with 19-Nov (Thursday) as we only have 5 working days in a week.
View Replies!
View Related
Date Function That Avoids Weekends
I just want to ask if we can create a formula that will not go to saturdays and sundays. example : I use =today()+3 so it means if today is tuesday the result is friday, if im going to open it tomorrow the result will adjust to saturday if that happens i want the day to be automatically go back on friday, the date itself, so it means i'll just wait on friday to get the result as "monday". It just avoid saturdays and sundays,. Is that possible? By the way, format would be m/d/yyyy.
View Replies!
View Related
Calculate Future Date Excluding Weekends
I am using the same structure to update another column, under the same sub: If Target.Column = 5 Then ' Select Case Range("d69").Value Case "CB1W" ActiveCell.Value = DateAdd("ww", 1, Now()) Case "CB2W" ActiveCell.Value = DateAdd("ww", 2, Now()) Case "CB3W" ActiveCell.Value = DateAdd("d", 21, Now()) Case "CB4W" ActiveCell.Value = DateAdd("d", 28, Now()) Case "CB5W" ActiveCell.Value = DateAdd("d", 35, Now()) Case "NI" ActiveCell.Value = "" Case Else ActiveCell.Value = "" End Select I would like to apply the code to the whole column but if I change Select Case Range("d69").Value to Select Case Range("d2:1000").Value I get a Type Mismatch Error (13) - (on Case "CB1W" ) - column d cells are data validation drop down boxes that get their value form a named ranged :Lead_Status. The column is also formatted as Text. (I applied the code to d69 because I have data already in the other cells which I don't want altered till I have the code working) I would also greatly appreciate your advice on how to incorporate the WEEKDAY function to exclude weekends for the DateAdd function.
View Replies!
View Related
Calculate Future Date But Omit Weekends
I want to calculate a date that is 28 days in the future. I don't want to exclude any days - However - if the end date falls on a weekend or holiday, I would like to push it out to the next business day. I currently have the weekends covered, but am stumped on the holidays. (For weekends, I am using the WEEKDAY function on a hidden sheet, and then the following 3 IF statements: IF today + 28 = Mon.-Fri., then give me today + 28. IF today + 28 = Sat., then give me today + 30. IF today + 28=Sun., then give me today + 29. I have tried adding an additional IF statement to address a specific holiday - namely, President's Day on 2/18/08, which is a Monday - but it won't add the extra day, because I think my initial IF statement re: Monday being today + 28 is overriding it.
View Replies!
View Related
Display Date, Excluding Weekends & Holidays
I have a report that starts with a desired date and generates dates backwards on when certain releases should come out. I would like the code to see the date, move it back a week, check to make sure it's not a weekend or holiday, then post it in the cell below. If it does happen to land on a weekend, it should then be the Friday before the weekend. If it is a holiday, it should be the date before the holiday, as long as it is not a Sunday (or another holiday). Attached is the workbook that I'm using. I'm starting with the desired date 'B7' and using the Holidays in a seperate worksheet. I know that in the current example, nothing will land on the weekend, unless the holiday pushes it back to one.
View Replies!
View Related
|
TRACKER
