Calculate average time elapsed for files in process

Hello everyone,

A user in another thread was able to help me organize my data in a useful way for this data set but now I am stuck on the calculation. We are trying to analyze three things in the activities logged on files between the time the file is submitted by a user and the time it is cleared by the committee:

1. How many days elapse between each individual activity
2. How many total days elapse between triage of the file (the activity associated with receiving the file) and a clearance being issued
3. The average of both of these

My anonymized dataset includes the data as we are able to export it from the database (first tab), the data reorganized using Unpivot to put each activity on its own line (second tab), a Pivot table organizing the activities so that they show up as a list grouped by each file number (third tab), and the ultimate end point I am trying to get to (fourth tab). I have not been able to find any functions in the Pivot tables that allow me to calculate on dates and am grateful for any ideas the forum can offer. I don't mind some manual work being involved as this report will only be produced annually, but I am hoping to make it as streamlined as I can, especially since the dataset will be much larger than this sample set.

Thanks!

Event activities Jan 1 - June 22 - Sample Data w desired endrpt.xlsx

This is amazing, thank you so much! From here I was able to use AVERAGEIF to give me an average of Total Elapsed Days in the Pivot table. Many thanks for your help!!!