1. ## TAT Tracker Help

B1: Completion date & time

Weekdays : Mon -Fri

Working hours: 9

Holidays

Calculate request completion time .Please consider Only Working days and hours Only need to consider for turn around time .

Please provide formula which will help me a lot.

Regards
Srinath

2. Hi Srinath,

When you say Working Hours: 9, what is the Start and End Time for the work hours? Is it from Morning 9:00 to evening 6:00?

3. Hi,

Try the following formula in C2:

=NETWORKDAYS(A2,B2,\$H\$2:\$H\$5)*(9.5/24)+NETWORKDAYS(B2,B2,\$H\$2:\$H\$5)*(IF(MOD(B2,1)>=17/24,MOD(B2,1)-17/24,IF(MOD(B2,1)<2.5/24,MOD(B2,1),0))-(9.5/24))+NETWORKDAYS(A2,A2,\$H\$2:\$H\$5)*(IF(MOD(A2,1)>=17/24,1-MOD(A2,1)+2.5/24,IF(MOD(A2,1)<2.5/24,2.5/24-MOD(A2,1),0))-(9.5/24))

This is considering that you have - Start Time in A2, End Time in B2, List of holidays excluding weekends are on H2:H6

4. Do let me know if you have any issues in updating the formula.

My question:
B2: Completion date & time
working hours 9hours 30 minutes. it's night shift... means....10/16/2015 evening 5.00 p.m starts and ends at 10/17/2015 morning 2.30 am.
1day means it should calculate this time (9 hour.30 mint).weekends & holidays need to exclude.