Trying to highlight hospital admissions that occured within 30 days of previous discharge

Greetings!
I am a Data Clark working in a hospital in Scotland, UK. As Excel skills are limited but I manage to get by with what I need to do,... however I am now faced with the following problem and hoping someone on the forum can point me in the right direction. Any help will be much appreciated.

I am working with hospital admission / discharge data using Excel 2010. My spreadsheet contains approximately 3000 rows. Amongst other columns, these are the ones that my problem relates to:
Column A = Hospital No.
Column B = Admission Date
Column C = Discharge Date

What I am trying to do is add two extra calculated columns:
Column D = Admission Count (based on admission date order, this column will be the number of times the patient has visited hospital based on the data in the table)
Column F = 30 days (this column needs to indicate if the admission date for this spell in hospital falls within 30 days or less of the previous discharge for this patient)

The workaround I currently have is to order my data into Hospital No and date order and then base my calculation of the preceding row. The problem with this is that my figures change each time I filter or order columns. I’ve spent ages trying to find a solution. On the web I’ve noticed that other people have tried to find a solution to this but it’s either using PowerPivot or their problem is slightly different to mine and I am not able to modify the formula to suite my data. As you may have guessed my Excel skills are limited.

Hope you can help. I will try attach some dummy data…

Hi glenbob and welcome to the forum.

Couple of clarification questions:
1) Will the data be in chronological order based on admission date?
2) For the "Admission Count" do you require that to be "as of this date this patient has been admitted X times"?

If you can attach a summy workbook (given the subject matter, make sure you desensitize the data!) that would help greatly.

There are three buttons below the window you reply in. Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

BSB

Hi BadlySpelledBuoy
Thanks for getting back to me.
1. The data isn't in order. I usually sort and save it in order but then a colleague will go into the spread sheet and change the order so that it's based on another column.
2. The admission count will be the number of times the patient has been admitted as of that date. IE it will not look forward but only into the past. Most patients will be a 1 but the readmissions will have a row with a 1, 2, 3,....
I'll have another go at adding the attachment.
Thanks

This in D2 and copied down:

Formula:

Please Login or Register  to view this content.

This in E2 and copied down:

Formula:

Please Login or Register  to view this content.

Note the second formula is an array formula and should be confirmed with Ctrl+Shft+Ent rather than just Ent.

BSB

BadlySpelledBuoy

It's perfect! You are an absolute star. Thank you so much.
I have been trying to solve this for ages now.
Very much appreciated.
Glenbob

No problem. Happy to help

Only easy when you know how!!

Take care.

BSB

Hi Shukla, whilst those formulas will give the same results in the current data layout, if the data is sorted into any other order (as is mentioned in post #3) it will give false results in some instances.

BSB