Calculation of Non Working Hrs

[ananya11]

Dear Excel Masters

I have Excel data where we capture team working data .basically agent attend the calls and resolve the problem . now I need to calculate how many hrs they are free and what time

working window is 9Am to 17:30 Pm Monday to Saturday

Thanks
Ananya

Agent In Time	Agent Out Time	Eng name						Non Woking time	Total Non Working hrs
12/02/2017 09:00	12/02/2017 10:00	ABC				ABC	12-Dec-17	11 to 13:30	5 Hrs
12/02/2017 13:30	12/02/2017 14:00	ABC						14 to 17:30
12/04/2017 09:15	12/04/2017 09:30	ABC
12/01/2017 16:05	12/01/2017 16:40	XYZ
12/01/2017 10:50	12/01/2017 11:00	XYZ
12/01/2017 14:15	12/01/2017 15:00	XYZ
12/01/2017 17:15	12/01/2017 17:30	XYZ
12/01/2017 10:30	12/01/2017 11:00	QRT
12/01/2017 13:30	12/01/2017 14:00	QRT
12/01/2017 18:00	12/01/2017 18:30	QRT

<tbody>
</tbody><colgroup><col><col><col><col span="4"><col><col><col></colgroup>

Agent In Time	Agent Out Time	Eng name						Non Working time	Total Non Working hrs
12/02/2017 09:00	12/02/2017 10:00	ABC				ABC	12-Dec-17	11 to 13:30	5 Hrs
12/02/2017 13:30	12/02/2017 14:00	ABC						14 to 17:30
12/04/2017 09:15	12/04/2017 09:30	ABC
12/01/2017 16:05	12/01/2017 16:40	XYZ
12/01/2017 10:50	12/01/2017 11:00	XYZ
12/01/2017 14:15	12/01/2017 15:00	XYZ
12/01/2017 17:15	12/01/2017 17:30	XYZ
12/01/2017 10:30	12/01/2017 11:00	QRT
12/01/2017 13:30	12/01/2017 14:00	QRT
12/01/2017 18:00	12/01/2017 18:30	QRT

<tbody>
</tbody><colgroup><col><col><col><col span="4"><col><col><col></colgroup>