2 Replies Latest reply: Feb 22, 2017 9:54 AM by Nate Schroeder RSS

    Calculating turnaround times with overlapping date ranges

    Nate Schroeder

      Hello,

       

      I am attempting to calculate turnaround times for requirements that are requested by form.  This is working great except for situations where the date ranges overlap - a form can have multiple concurrent requirements that have been requested, and I don't want the turnaround times to "stack".

       

      For example, using the data below, the requirement TAT for form 1 would be 36 days.  This is easy because the date ranges to not overlap.  But using my current method, form 2 shows a requirement TAT of 81 days.  This is not correct because the date ranges overlap.  I would like the total TAT in this situation to calculate to 50 days.

       

      Here's the formula I'm currently using: =avg(aggr(sum(distinct [Requirement Received Date]-[Requirement Request Date]),[FormNum]))


      I appreciate any help you can provide.  Thank you!

       

       

      FormNumRequirement TypeRequirement RequestedRequirement ReceivedTAT (days)
      1A1/1/20172/1/201731
      1B2/15/20172/20/20175
      2C1/1/20172/1/201731
      2D1/15/20172/20/201736