Average time elapsed in hh:mm format between 2 dates after accounting for weekends and business hours

[Yemjay]

Hi

I have a ticket resolution data as shown below. I would need this data to monitor my SLA compliance.

Excel 2007 32 bit

	A	B	C	D	E	F
Ticket 1
Ticket 2
Ticket 3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]Ticket[/TD]
[TD="align: center"]Assigned Date[/TD]
[TD="align: center"]Acknowledged Date[/TD]
[TD="align: center"]Resolved Date[/TD]
[TD="align: center"]Time Elapsed (Assigned To Acknowledged)[/TD]
[TD="align: center"]Time Elapsed (Assigned to Resolved)[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]03-08-2017 21:06[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]04-08-2017 15:16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]07-08-2017 18:29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]04-08-2017 15:03[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]07-08-2017 16:27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]09-08-2017 23:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]14-09-2017 00:51[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]14-09-2017 00:51[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]16-09-2017 08:51[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Ticket Sheet​

I have a few set of conditions, which I would want to build into my time elapsed formula -
1. If the ticket assigned date is before 8.30 AM that day (i.e. for example Ticket 3 was assigned at 00:31) the formula should consider assigned time by default as 8.30 AM on that day itself. In my ticket 3 example, the new assigned date would be 14-09-2017 08:30. Time elapsed should be calculated using this date and time.

2. If the ticket assigned date is after 5.30 PM that day (i.e. Ticket 1 in my example) the formula should consider assigned time by default as 8.30 AM on the next working day. In my ticket 1 example, the new assigned date would be 04-08-2017 08:30. Suppose the next day is a weekend (Sat and Sun are weekends) then new date would be 06-08-2017 08:30.

3. When calculating Time elapsed (both acknowledged and resolved) it would need the unit to be in HH:MM format.

4. The time elapsed should be calculated after removing the weekends and non business hours, if any. i.e in Ticket 2 example, the assigned date (04-08-2017) is a Friday and the Acknowledged and Resolved dates are Monday and Wednesday respectively. So time elapsed in this case should be the hours between these dates after removing the weekends and non-business hours within the week days.

Business Hours are - 8.30 AM to 5.30 PM and Business days are Monday to Friday.

This would be a typical support call scenario for calculating SLAs.
I had tried accounting for the business hours by the below logic.

Excel 2007 32 bit

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8D8D8]#D8D8D8[/URL] , align: center"]Assigned[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8D8D8]#D8D8D8[/URL] , align: center"]Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8D8D8]#D8D8D8[/URL] , align: center"]Time[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8D8D8]#D8D8D8[/URL] "]After 8.30 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8D8D8]#D8D8D8[/URL] "]Before 5.30 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8D8D8]#D8D8D8[/URL] "]New Assigned Date[/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]03-08-2017 21:06[/TD]
[TD="align: center"]03-08-2017[/TD]
[TD="align: center"]9:06 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]No[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]04-08-2017 08:30:00[/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]14-09-2017 00:51[/TD]
[TD="align: center"]14-09-2017[/TD]
[TD="align: center"]12:51 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]No[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Yes[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]14-09-2017 08:30:00[/TD]

</tbody>

Ticket Sheet​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]D28[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C28>TIME(8,30,0),"Yes","No"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]E28[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C28<TIME(17,30,0),"Yes","No"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]F28[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]D28="Yes",IF(E28="No",TEXT([COLOR=0)]B28+1,"dd-mm-yyyy"[/COLOR])&" "&TEXT([COLOR=0)]TIME([COLOR=rgb(0]8,30,0[/COLOR]),"hh:mm:ss"[/COLOR]),A28),TEXT(B28,"dd-mm-yyyy")&" "&TEXT(TIME([COLOR=0)]8,30,0[/COLOR]),"hh:mm:ss")[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]D29[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C29>TIME(8,30,0),"Yes","No"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]E29[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]C29<TIME(17,30,0),"Yes","No"[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]F29[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]D29="Yes",IF(E29="No",TEXT([COLOR=0)]B29+1,"dd-mm-yyyy"[/COLOR])&" "&TEXT([COLOR=0)]TIME([COLOR=rgb(0]8,30,0[/COLOR]),"hh:mm:ss"[/COLOR]),A29),TEXT(B29,"dd-mm-yyyy")&" "&TEXT(TIME([COLOR=0)]8,30,0[/COLOR]),"hh:mm:ss")[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



But when taking the elapsed time using the newly derived assigned dates, excel only calculates difference between these dates with an assumption that both times are in a single day. I guess excel does not look into the difference in dates. Also I am not sure how I can account for the weekends in the above logic.

Would be really helpful if some one could suggest some logic to calculate the elapsed time incorporating for my conditions.

Thanks.