Results 1 to 5 of 5

Thread: TAT Tracker Help

  1. #1

    TAT Tracker Help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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



  2. #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. #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. #4
    Do let me know if you have any issues in updating the formula.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •