Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Thread: I Need a formula to calculate the Turn Around Time

  1. #1
    New Member
    Join Date
    Dec 2010
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default I Need a formula to calculate the Turn Around Time

    I need a formula to calculate Turn Around Time. Find below the sample data and details for your reference.

    Received Date & Time: 10/29/2010 3:27:00 PM
    Completed Date & Time: 11/1/2010 3:57:32 AM
    Business Hours: 08:00 AM to 05:00 PM
    Non Business hours: 05:00 PM to 08:00 AM
    Weekdays : Monday to Friday
    Weekends: Saturday & Sunday

    If I calculate TAT for this time period it should show me 1 hour 33 minutes, why because business hours are closing by 5:00 PM on 10/29 so it should not calculate duration after 5:00 PM to until 08:00 AM on next day, however 10/29 is Friday so formula should calculate the duration from 10/29 Friday 3:37 PM to 10/29 5:00 PM and again it should calculate from 8:00 AM on 11/01 Monday and formula should skip Saturday & Sunday.

    In this scenario I have completed the task before 8:00 AM on 11/01 so it should show duration of 1hour 33 minutes.

    Please let me know if you need any more details

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I Need a formual to calculate the Turn Around Time

    If you have start time/date in A2 and end time/date in B2 you can use this formula in C2

    =(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","8:00")

    format C2 as [h]:mm

  3. #3
    New Member
    Join Date
    Dec 2010
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I Need a formual to calculate the Turn Around Time

    Thank youuuuuuuuuuu so much

    Formula is working for me

    Thank you so much for the great help

  4. #4
    New Member
    Join Date
    Dec 2010
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I Need a formual to calculate the Turn Around Time

    How do I add Holiday list to the formula "NETWORKDAYS"

  5. #5
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I Need a formual to calculate the Turn Around Time

    You can add the holiday range as the 3rd argument in each of the NETWORKDAYS functions, so if holidays are listede in H1:H10 change formula to the following:

    =(NETWORKDAYS(A2,B2,H$1:H$10)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2,H$1:H$10),MEDIAN(MOD(B2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2,H$1:H$10)*MOD(A2,1),"17:00","8:00")

  6. #6
    New Member
    Join Date
    Dec 2010
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I Need a formual to calculate the Turn Around Time

    Thank you so much

  7. #7
    New Member
    Join Date
    Jan 2012
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I Need a formual to calculate the Turn Around Time

    Quote Originally Posted by barry houdini View Post
    If you have start time/date in A2 and end time/date in B2 you can use this formula in C2

    =(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","8:00")

    format C2 as [h]:mm
    It is working, thanks a ton...!!!

  8. #8
    New Member
    Join Date
    Sep 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: I Need a formual to calculate the Turn Around Time

    Quote Originally Posted by barry houdini View Post
    If you have start time/date in A2 and end time/date in B2 you can use this formula in C2

    =(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","8:00")

    format C2 as [h]:mm
    This is an good help,

    I would like to know if we have date and time in a different coloumn eg A2 has start date and B2 start time and then C2 end date, D2 end time in 24 hour format.
    can you please advise on this.


  9. #9
    New Member
    Join Date
    Mar 2016
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I Need a formual to calculate the Turn Around Time

    Hi Barry! Can you explain the formula for me please?


    Quote Originally Posted by barry houdini View Post
    If you have start time/date in A2 and end time/date in B2 you can use this formula in C2

    =(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","8:00")

    format C2 as [h]:mm

  10. #10
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    2,160
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I Need a formual to calculate the Turn Around Time

    Hi,

    The safest way out is to rely on Barry's formula in post # 5 and amend it with your own "Holidays" list ...i.e your second Saturday and your fourth Saturday and Sunday ...

    HTH

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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