Calculate average durations between dates

Chris Walker

I have an original sheet with hundreds of rows of information (Source Sheet) that I am looking to summarize calculations in to a second sheet (Summary Sheet), in order to feed in to a dashboard. Source Sheet has two date fields that I am interested in - lets refer to them as an 'open' and 'close' date.

I would like to calculate the average duration between open and close for the entire sheet.

Is there a calculation that can accommodate this directly in to the Summary Sheet? Without having to calculate the duration between open and close for each record in the Source Sheet? My sheet is large as it is, and would prefer to not expand, if possible.

Appreciate the help - can't seem to find exactly what I'm looking for in the community.

Paul Newcome

Unfortunately not. You would need to have the actual duration calculated on the source sheet before you could pull the average.

Chris Walker

Forgot to include that some of the date fields may be blank or include 'N/A' text within the cell - so this would need to be considered as well.

Paul Newcome

Unfortunately not. You would need to have the actual duration calculated on the source sheet before you could pull the average.