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[TABLE="width: 703"]
<tbody>[TR]
[TD]Creation Time[/TD]
[TD]Closed Time[/TD]
[TD]Working hours - start[/TD]
[TD]Working hours - end[/TD]
[TD]TAT[/TD]
[TD]Holidays[/TD]
[/TR]
[TR]
[TD="align: right"]12/21/2012 17:35[/TD]
[TD="align: right"]12/24/2012 6:24[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]1/1/2013[/TD]
[/TR]
[TR]
[TD="align: right"]12/21/2012 21:59[/TD]
[TD="align: right"]12/24/2012 6:41[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]1/21/2013[/TD]
[/TR]
[TR]
[TD="align: right"]12/21/2012 15:24[/TD]
[TD="align: right"]12/24/2012 6:50[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]5/27/2013[/TD]
[/TR]
[TR]
[TD="align: right"]12/21/2012 16:12[/TD]
[TD="align: right"]12/24/2012 6:53[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]18:00[/TD]
[TD]Not Met TAT[/TD]
[TD="align: right"]7/4/2013[/TD]
[/TR]
[TR]
[TD="align: right"]12/21/2012 22:30[/TD]
[TD="align: right"]12/24/2012 7:14[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]18:00[/TD]
[TD]Not Met TAT[/TD]
[TD="align: right"]9/2/2013[/TD]
[/TR]
[TR]
[TD="align: right"]12/21/2012 23:09[/TD]
[TD="align: right"]12/24/2012 8:09[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]18:00[/TD]
[TD]Not Met TAT[/TD]
[TD="align: right"]11/28/2013[/TD]
[/TR]
[TR]
[TD="align: right"]12/21/2012 22:25[/TD]
[TD="align: right"]12/24/2012 8:36[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]18:00[/TD]
[TD]Not Met TAT[/TD]
[TD="align: right"]11/29/2013[/TD]
[/TR]
[TR]
[TD="align: right"]12/22/2012 9:49[/TD]
[TD="align: right"]12/24/2012 8:59[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]18:00[/TD]
[TD]Not Met TAT[/TD]
[TD="align: right"]12/25/2013[/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="2"><col><col></colgroup>[/TABLE]


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...