Calculate the total TAT hours by excluding the non-working hours, weekends and declared holidays

[pvk_79]

Hi All

I want to calculate the total TAT hours (refer column E) by excluding the non-working hours, weekends and declared holidays.

Non-workings hours are – 6:01 PM to 2:59 AM EST
Working hours for us – 3 AM to 6 PM EST
Weekends – every Saturday and Sunday

A B C D E F

Creation Time	Closed Time	Working hours - start	Working hours - end	TAT	Holidays
12/21/2012 17:35	12/24/2012 6:24	3:00	18:00	15:00	1/1/2013
12/21/2012 21:59	12/24/2012 6:41	3:00	18:00	15:00	1/21/2013
12/21/2012 15:24	12/24/2012 6:50	3:00	18:00	15:00	5/27/2013
12/21/2012 16:12	12/24/2012 6:53	3:00	18:00	Not Met TAT	7/4/2013
12/21/2012 22:30	12/24/2012 7:14	3:00	18:00	Not Met TAT	9/2/2013
12/21/2012 23:09	12/24/2012 8:09	3:00	18:00	Not Met TAT	11/28/2013
12/21/2012 22:25	12/24/2012 8:36	3:00	18:00	Not Met TAT	11/29/2013
12/22/2012 9:49	12/24/2012 8:59	3:00	18:00	Not Met TAT	12/25/2013

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

Your help will be highly appreciated. Thanks in advance.

 

[guptamols]

Actually, yes, you have some references to B2 which should be A2 - those marked in red

It works, however it would be great if you can explain the logic...