Calculating Average Wait Times Between Multiple Procedures Within a Single Event

[ploweyj2]

I am not sure where to start with this.  I need to calculate the time between stages for each unique accession number for Mohs Surgery. From there I need to calculate the average wait time between stages for all Accession numbers with multiple stages . To be brief and not give a long explanation of Mohs surgery when a patient has Mohs surgery it may take multiple stages to remove all the tumor for a given proceedure or it may be removed in the first stage.  The table looks like the following

Table: Stages

Accession Number	Stage #	Time In	Time Out
SMM-001	stage 1	8:00	8:15
SMM-001	stage 2	8:36	8:50
SMM-001	stage 3	9:45	10:00
SMM-002	stage 1	8:30	9:00
SMM-003	stage 1	10:00	10:20
SMM-003	stage 2	10:30	10:40

"time in" represents the start of surgery and "time out: represents the end of surgery for that stage.  Accesion SMM-001 would have two wait times of 21 and 55 min between stages, SMM-002 would have no wait time and could be ignored, and SMM-003 would have 10 min returning and average wait time of 28.7 min between stages.  Any help would be greatly appreciated.  

[LivioLanzo · Accepted solution]

Hello @ploweyj2

download my sample file here: https://1drv.ms/u/s!AiiWkkwHZChHj1L7XRocERSQVMrN