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.

 

[pvk_79]

This doesn't work ... =(NETWORKDAYS(A2,B2)-1)*(D2-C2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),D2,C2),D2)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),D2,C2)

 

[pvk_79]

Hi - Would you please suggest any pointers. I've gone through the posts available on this portal related to TAT and tried to collect the info in bits and pieces however the final formulae doesn't seem working for me.....

Thanks a lot in advance

 

[barry houdini]

=(NETWORKDAYS(A2,B2)-1)*(D2-C2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),D2,C2),D2)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),D2,C2)

This looks right to me, although you need to insert the holiday range into all 3 NETWORKDAYS functions. If you do that and you still get incorrect results then can you give a specific example where you don't get what you expect

 

[pvk_79]

Thank you Barry, I missed that earlier hence it has been corrected now .....

=(NETWORKDAYS(A2,B2,F2:F9)-1)*(D2-C2)+IF(NETWORKDAYS(B2,B2,F2:F9),MEDIAN(MOD(B2,1),D2,C2),D2)-MEDIAN(NETWORKDAYS(B2,B2,F2:F9)*MOD(B2,1),D2,C2)

however I'm still facing the problems -

i.e. please have a look at the last row - the start time is 12/22/2012 9:49:00 AM and end time is 12/24/2012 8:59:00 AM, hence the TAT should be roughly 6 hours however its shows zero to me ... can you please guide me here in case I'm doing something wrong....

I also checked the other rows and it shows wrong calculations of TAT
i.e. row 2 has start time 12/21/2012 5:35:00 PM and end time 12/24/2012 6:24:00 AM hence TAT should be roughly 4 hours however its still shows 15 hours TAT

The TAT cell is in H:MM format ...

 

[barry houdini]

=(NETWORKDAYS(A2,B2,F2:F9)-1)*(D2-C2)+IF(NETWORKDAYS(B2,B2,F2:F9),MEDIAN(MOD(B2,1),D2,C2),D2)-MEDIAN(NETWORKDAYS(B2,B2,F2:F9)*MOD(B2,1),D2,C2)

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

 

[pvk_79]

Hi Barry,

That's perfect! Thank you for your guidnace, appreciate all your help....

Cheers
Pankaj

 

[pvk_79]

changed the holidays into a static array .....

=(NETWORKDAYS(A2,B2,{41275;41295;41421;41459;41519;41606;41607;41633})-1)*(D2-C2)+IF(NETWORKDAYS(B2,B2,{41275;41295;41421;41459;41519;41606;41607;41633}),MEDIAN(MOD(B2,1),D2,C2),D2)-MEDIAN(NETWORKDAYS(A2,A2,{41275;41295;41421;41459;41519;41606;41607;41633})*MOD(A2,1),D2,C2)

 

[pvk_79]

Guys - BEWARE

I just checked and the above formulae has some problems when the TAT crosses 24 hours - I mean it gives wrong results

 

[pvk_79]

To overcome this problem use the TAT cells strictly in [h]:mm format - will resolve this problem