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

    Calculating turnaround times with overlapping date ranges

    Nate Schroeder



      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)