TAT Tracker Help

[srinath2015]

Need your help to create a tracker to check the status of task completion. Please check below heads need to consider.

A1 :Receive date& time


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

[cbatrody]

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?

[cbatrody]

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

[cbatrody]

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

[srinath2015]

Thanks a lot for your reply...
My question:
A1: Receive date & time
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.
Please reply if you need any other information.

Thanks in Advance
Srinath