computing the number of business hours between 2 dates.
Given the following information: - Business Hours: 8am -5pm (8:00-17:00) - Business Days: Mon-Fri - if entry queued falls on a weekend or beyond the business hours, it should count the first hour on the next business hour.
Example: scenario 1 = Starts Sunday, 10am and Ends Monday 9am; count of hours should be 1 scenario 2 = starts on Monday 6pm and ends on Tuesday 10am; count of hours should be 2
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?
I'm trying to find a formula for calculating the number of business hours a service request may be open; this request may have been opened and even closed outside of business hours. I found a formula that appears to be working:
Where i want add Business Hours/Days to get the Target Date.
Working hours: 8:00 AM to 19:00 PM
ND (Maximum 1 Day 11 hours) 3D (Maximum 3 Day 11 hours) 4D (Maximum 4 Day 11 hours) 5D (Maximum 5 Day 11 hours) Threshhold - 16:30 PM Weekends to be Excluded.
Holidays to be Excluded based on Country. Say I have 2 countries India and America, where America has holiday on 12/24/2012 but India does not have holiday on that Day. Hence while calculating Tgt date, India should be calculated normally, for America holiday date need to excluded(There will be a Holiday table based on the countries, not limited to 2 countries).
ex: 1) A ticket opened at 12/26/2012 8:56 AM; Target date will be 12/26/2012 19:00, in this case its 1 day 10:04 hours added to the Opened Time.
2) A ticket opened at 12/26/2012 16:56 PM; Target date will be 12/29/2012 19:00, in this case ticket opened after threshold time of 16:30, Technically ticket should start from next day i.e 12/28/2012 8:00 AM and 1 day 11 hours added to this date.
3) A ticket opened at 12/26/2012 7:56 AM(Before the begin of Working hours); Target date will be 12/26/2012 19:00, in this case its 1 day 11:00 hours added to the Opened Time.
4) A ticket opened at 12/21/2012 17:56 PM(After Threshold time); Target date will be 12/24/2012 19:00, in this case its 1 day 11:00 hours added to the Opened Time. If the above ticket belonged to America, then holiday dates(both 24 & 25) need to be skipped and Tgt date will be 12/27/2012 19:00.
Here is the Sample Data
Country Ticket Start Date/ Time Tgt Date/Time Expected Tgt Date/ Time
America ID000123 12/21/2012 18:20
I need results to be appear in the "Tgt Date/Time" column. I have also provided results in "Expected Tgt Date/ Time".
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)
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.
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.
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
I have a requirement of calculating time spent on ticket, basically a difference between the time a ticket was logged and when it was resolved.
First, working days are Saturday through Wednesday, i.e weekends are Thursday & Friday.
Second, there are different resolver groups or teams which work on a ticket. These teams have different working hours, some work from 07:30 to 15:30 on weekdays, and others work 24/7. Each ticket will be assigned only to one resolver group.I also need to consider holidays.
So, my requirement is to check the resolver group for each ticket, and decide the working hours based on it. After this check has to be done for holidays, and then difference between logged time and resolved time needs to caluculated in minutes.
Tickets can be logged at any time of the day / week, it may not always be logged during working hours only. in such cases calculate should consider next business day / hour as the start time.
Raw data for tickets is available in =Sheet1!$A$1:$E$21
I'm working on a complex A/R aging summary. So Ill have couple of question, but right now I'm trying to have excel automatically keep track of the A/R process. For instance every business day I would like the number of days the Invoice is outstanding to go up by 1. So when I get to work on monday the invoices that have been due for 20 days will now show 21 without me touching it.
I'm attempting to make a simple time sheet for a handful of employees. I'd like to enter the clock in time and clock out time for each day. The end cell should be the running total for the week. The tricky part for me is having the formula subtract an hour for each day that is over 5 hours.
I am looking for a formula that looks at a date and could add business days to it.
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
i don't know why this one stumps me but it does. I have a production schedule which shows how many hours and minutes are remaining for a given production run, i.e. 6.4 or 3.2. i want to do two things with these values. 1) add the 6.4 to the current time, something like a1 + now(0) to get the time the production run should end.
2) i want to add the number of minutes from a calculation to the current time, ie. 10:14 AM + 122 minutes = actual time the production ends. Nothing i do seems to give a desired result - including formatting the cell to any variation of the time format - including custom.
I'm having great difficulty adding hours to dates in Excel. In cell A1 I have a number which is calculated from a formula, it will normally return whole number values between 1 and 100. In cell A2 I have a start date and time i.e. 14/2/07 06:00:00 in cell A3 I want to add the number in A1 as hours to the date in cell A2. I am using the formula in A3 as =A2+TIME(A1,0,0) it seems to work okay for numbers that don't take the date onto the next day but if I try to add 24 hrs or 48 hrs for example the date still stays the same.
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?
So I'm having a problem trying to make this scheduling sheet.. What I did was row a would be the employee list then b,c,d and so on are mon-sun Originally in b1 I would put like 9am-5pm and c1 8-4 and so on and in row i I put the total and added a sum function but somehow it wasn't able to calculate. So then I redid the whole thing this time in row b1 i put 9am and c1 5pm and so on and again I put the total and added the function and it still came out wrong..
I am trying to add duration time for song files. 4 minutes and 32 seconds + 3 minutes 45 seconds, etc. etc. to get total hours and then to total to days. The range of cells is over 5,000 and I am trying to find out how long a playlist will play.
I have a data of complaints where I need to present it to the Management in such a way that the SLA period of 8 hours does not pass. Our office working hours are 7 AM till 7 PM. The complaint received should be escalated to concern section within 8 Hours of SLA time. I have the list of dates with received time. The complaint which could not be escalated today would be escalated next day. In this case is should deduct 12 Hours (7 PM to 7 AM, Non-working hours) from the time. How can I insert escalation date so as that it would deduct non working hours from it.
A B 1 4/1/09 12:15 2 4/2/09 10:00 3 4/4/09 8:10 4 4/6/09 9:00 5 4/8/09 5:00 6 4/11/09 7:00
I need to add up hours worked of last 3 days including current day. The date column does not contain everyday's date. How do I formulate a cell for date entry and another cell will show the sum of last 3 days including date not shown and date entered? E.g. If I enter date 4/6/09, result should be 17:10 hrs; 4/11/09, result should be 7:00.
i have a column "Received On" contains the datetime of a mail received , it is in BST, sometimes it should be in GMT depends on the date or year, i'm not sure. So what my query is, in another column, i have to check if the datetime in "Received On' is in BST, convert this datetime to GMT, otherwise if this time is in GMT, i don't need to add anything just copy that datetime alone.
eg: ie in ColumnA is the Datetime value
if ColumnA=BST then ColumnB=ColumnA+1/24 (ie convert BST to GMT) else if ColumnA=GMT then ColumnB=ColumnA (ie not need to change, becz already columnA is in GMT)