Count non-consecutive days as new occurence to be used in average

Hi All,

I was asked to go through a utilization log from last year to find the average duration of service (Total number of service days per patient / Total number of patients). I was off to a great start and things were going well until I was informed that the folks requesting the data want non-consecutive days of service to be treated as a "new admit." Unfortunately, a "new admit" field was never thought of when the worksheet was made. So, based on the data I have available, I need to try and sort through the gaps in date of service and increase the patient count for every gap in a consecutive date range. I have a few thousand lines of data to file through and I was hoping someone may have a ready made solution or could possibly steer me down the correct path. I have a very basic, sample worksheet I am attaching. I also show the data as it is currently being calulated and what I would like it to be calculated as, if a solution can be found for the date gap issue.

Thank you for your help and all you do for the forum.

Patrick

Thank you very much Melvinrobb. Worked like a charm.


Patrick