Calculating difference between opened date/time and closed date/time and excluding weekends, holidays and off business hours

[RishiKapoor]

I have an excel sheet that lists the opened and closed date/time for a ticket.
Our SLA’s are based upon created date/time during the coverage hours – 8am ET until 5pm ET (weekends and national holiday’s, both US and India, excluded).
If ticket is received prior to 8am ET or after 5pm ET, the clock doesn’t start until the coverage begins for the business day.
Example: case is received at 11pm ET, because our coverage begins at 8am on each business day, the SLA for this case would begin at 8am ET on the next business day.
Example: case is received on Saturday at 12:03am ET, SLA for the case begins on next business day at 8am ET.
I am having a hard time to figure out the formula in excel. NETWORKDAYS does not completely work since I need to figure out a way to calculate SLA on the basis of time too.



[TABLE="width: 439"]
<tbody>[TR]
[TD]Case Number[/TD]
[TD]Case Owner[/TD]
[TD]Date Time Opened[/TD]
[TD]Date/Time Closed[/TD]
[/TR]
[TR]
[TD]1901-3699490[/TD]
[TD]Rishi Kapoor[/TD]
[TD]1/1/2019 20:58[/TD]
[TD]1/3/2019 12:02[/TD]
[/TR]
[TR]
[TD]1901-3699622[/TD]
[TD]Sue Palmer[/TD]
[TD]1/2/2019 4:01[/TD]
[TD]1/3/2019 13:06[/TD]
[/TR]
[TR]
[TD]1901-3699643[/TD]
[TD]Vane Ahuja[/TD]
[TD]1/2/2019 4:28[/TD]
[TD]1/2/2019 7:44[/TD]
[/TR]
[TR]
[TD]1901-3699688[/TD]
[TD]Rishi Kapoor[/TD]
[TD]1/2/2019 5:09[/TD]
[TD]1/2/2019 8:43[/TD]
[/TR]
[TR]
[TD]1901-3699690[/TD]
[TD]Rishi Kapoor[/TD]
[TD]1/2/2019 5:10[/TD]
[TD]1/2/2019 8:50[/TD]
[/TR]
[TR]
[TD]1901-3699694[/TD]
[TD]Rishi Kapoor[/TD]
[TD]1/2/2019 5:12[/TD]
[TD]1/2/2019 9:01[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Please provide the expected results for all 6 of those cases.
Also, is your date format DD/MM/YYYY or MM/DD/YYYY ?

 

[RishiKapoor]

Hi Gerald,

Thank you for the response. The data here is in mm/dd/yyyy format. Ideally I would need the data to be in number of days format but that is something I can easily do if I can get the number of hours between the 2 dates/times keeping the working hours in mind. Also the data above is already in EST format.

Thanks,
Rishi

 

[RishiKapoor]

For example the first row. The case came in at 8:58 PM EST on 1st. The SLA would start at 8 AM on 2nd Jan and run till 5 PM EST on the 2nd. The SLA would stop again since it is outside working hours and would start again for 4 hours on 3rd. So the total time it took to process the first case is 13 hours

Thanks

 

[Gerald Higgins]

Same question again . . .

Please provide the expected results for all 6 of those cases.

 

[RishiKapoor]

Hi Gerald,

Please see below:

[TABLE="width: 1105"]
<colgroup><col width="221" span="5" style="width:166pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 221"]Case Number[/TD]
[TD="class: xl63, width: 221"]Case Owner[/TD]
[TD="class: xl63, width: 221"]Date Time Opened[/TD]
[TD="class: xl63, width: 221"]Date/Time Closed[/TD]
[TD="class: xl63, width: 221"]Time to Closure[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 221"]1901-3699490[/TD]
[TD="class: xl63, width: 221"]Rishi Kapoor[/TD]
[TD="class: xl64, width: 221, align: right"]1/1/2019 20:58[/TD]
[TD="class: xl64, width: 221, align: right"]1/3/2019 12:02[/TD]
[TD="class: xl65, align: right"]13[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 221"]1901-3699622[/TD]
[TD="class: xl63, width: 221"]Sue Palmer[/TD]
[TD="class: xl64, width: 221, align: right"]1/2/2019 4:01[/TD]
[TD="class: xl64, width: 221, align: right"]1/3/2019 13:06[/TD]
[TD="class: xl65, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 221"]1901-3699643[/TD]
[TD="class: xl63, width: 221"]Vane Ahuja[/TD]
[TD="class: xl64, width: 221, align: right"]1/2/2019 4:28[/TD]
[TD="class: xl64, width: 221, align: right"]1/2/2019 7:44[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 221"]1901-3699688[/TD]
[TD="class: xl63, width: 221"]Rishi Kapoor[/TD]
[TD="class: xl64, width: 221, align: right"]1/2/2019 5:09[/TD]
[TD="class: xl64, width: 221, align: right"]1/2/2019 8:43[/TD]
[TD="class: xl65, align: right"]0.43[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 221"]1901-3699690[/TD]
[TD="class: xl63, width: 221"]Rishi Kapoor[/TD]
[TD="class: xl64, width: 221, align: right"]1/2/2019 5:10[/TD]
[TD="class: xl64, width: 221, align: right"]1/2/2019 8:50[/TD]
[TD="class: xl65, align: right"]0.5[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 221"]1901-3699694[/TD]
[TD="class: xl63, width: 221"]Rishi Kapoor[/TD]
[TD="class: xl64, width: 221, align: right"]1/2/2019 5:12[/TD]
[TD="class: xl64, width: 221, align: right"]1/2/2019 9:01[/TD]
[TD="class: xl65, align: right"]1

[/TD]
[/TR]
</tbody>[/TABLE]


[FONT=&quot]If the case is received before the 8am ET business day, the create date/time is 8am on the next business day. If the case is closed after 5pm ET, the case closed date is at 8am on the next business day.
[/FONT]

 

[Gerald Higgins]

In post #6 , I think you have stated the "Time to Closure" incorrectly in some cases.

For case 1901-3699490, I assume you treat 2nd Jan as a working day, therefore I think the time to closure is 13h 2m, is that correct ?
For the next one, I think it is 14h 5m, is that correct ?
For case 1901-3699643, I take it the answer is 0 because Time Opened and Time Closed are both on the same date, before 08:00. Is that correct ?
For the next two, I think the results should be 0h 43m, and 0h 50m, yes ? Which are different from 0.43 hours and 0.5 hours.
For the final one, I think it is 1h 1m, correct ?

 

[RishiKapoor]

You are correct Gerald. Thank you!

 

[Gerald Higgins]

OK, I'm going offline now, but will take a look again tomorrow.

 

[RishiKapoor]

Sure thing. Thank you Gerald