Count of consecutive dates as single occasion per patient

Hey I'm new to this forum but I have a question that I hope can be easily answered by someone more knowledgeable in Excel functions than myself. I looked at an old thread where someone was wanting to count the consecutive dates as a single occasion and the formula given works, however it utilizes the Networkdays function and I need to include any and all dates. This is for a count of consecutive dates per patient admission so weekends and holidays are to be included in this count.

I couldn't figure out how to change this function to allow for weekends. Thank you. I greatly appreciate any help I can get with this. This is a report request that I have to manually put together and I'm just stumped.



Function to count consecutive dates as one occasion
=--((NETWORKDAYS(SUM(c1)*(B1=B2),c2)-1)>1)

Column A Column B Column C Column D ( I would then use this column in a pivot table to calculate)
pat name pat id date of service # Admissions

ex:

12345 07/22/2013 1
12345 07/23/2013 0
12345 11/01/2013 1
67890 05/04/2014 1
67890 05/05/2014 0 etc,

Hi,

Welcome to the Forum.

Can you please post a sample template in excel format with enough raw data and a desired result tab in it?

Hi, thank you for your response. It happens that they have once again changed the scope of this query so I will have to fine tune the details and post the sample template you requested. I now need to not only count consecutive admission dates but also for only a particular doctor and for only certain CPT codes. Much of this can be done in SQL but I am hoping to bring it together in Excel with the help of this forum.

Marlene

I am attaching a template of sample patient data that I would expect to encounter. The Networkdays function used in column D works fine, but excludes weekend days which I need to be included in this count of admissions.

Thank you very much for any help you can provide!!

Marlene

Hi,

I could not comprehend the requirement by going through the excel sheet uploaded by you, my apologies. Can you please insert a desired result tab and mock the output?