Calculating readmissions within 30 day and 365 days intervals

narrowdave

New Member
Joined
May 3, 2017
Messages
19
Hi – first posting on here, so hopefully I give the right level of detail… I seem to need some help I’ve set up a spreadsheet to monitor numerous aspects of admissions and discharges from mental health hospitals across a UK region. Data is entered into an excel table – there are loads of connected pivot charts which are hidden and then loads of the data is brought together and shown via a dashboard type setup…

All’s fine – I’m a nurse rather than a techie, so much learning has occurred… I have one remaining challenge however… I need to be able to show readmission data. i.e. number of readmissions within a 30 day period (since recording began); number of readmissions within the last 30 days (i.e. ending today); number of readmissions within a 365 day period (since data collection began); and number of readmissions within the last 365 day period (i.e. ending today)…

Once in use the spreadsheet will rapidly grow in terms of number of patients being monitored and users of the system may need to access real time data at any point in time.

My spreadsheet is formatted as follows (dummy data included AND only showing the columns relevant to current query):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]H[/TD]
[TD]Z[/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]Unique Patient Identifier[/TD]
[TD]Admission Date[/TD]
[TD]Discharge Date[/TD]
[TD]Admission number[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]265 409 8111[/TD]
[TD]12/12/2017[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]123 456 7890[/TD]
[TD]25/01/2017[/TD]
[TD]31/01/2016[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]123 765 8976[/TD]
[TD]12/02/2017[/TD]
[TD]28/02/2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]123 456 7890[/TD]
[TD]09/03/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]

As I said, the number of rows will grow rapidly…

So what I need are some formulae which will:


  • identify duplicate Unique Patient Identifiers – i.e. pick out readmissions
  • then compare measure the interval between admission date and previous discharge dates for those patients (of course some patients will still be in hospital so have no discharge date – for others there may be no readmissions i.e. it's their first admission).
  • Then tell me how many readmissions occurred within:
    • 30 days ending on the day of the query
    • 365 days ending on the day of the query
    • Any 30 day interval since recording began
    • Any 365 day interval since recording began

I’m competent but not good at writing formulae (getting better though!!). I would appreciate any suggestions, advice, assistance that people can offer…

Thx
 
OK what I have so far, I added some data to help check a few things.

Re-ad Last 30/365 looks at any re-ads in previous 30/365 days based on the current date.

Re-ad Any looks at the re-ad date & compares to the previous disch date, if less than 30/365 days.


Code:
[TABLE="width: 1153"]
<tbody>[TR]
[TD]Patient Identifier[/TD]
[TD]Date of Birth[/TD]
[TD]Age[/TD]
[TD]Gender[/TD]
[TD]Admission Date[/TD]
[TD]Discharge Date[/TD]
[TD]Length of Stay[/TD]
[TD]Admission number[/TD]
[TD]Re-ad Last 30 days[/TD]
[TD]Re-ad Last 365 days[/TD]
[TD][/TD]
[TD]Re-ad Any 30 days[/TD]
[TD]Re-ad Any 365 days[/TD]
[/TR]
[TR]
[TD]65 409 8111[/TD]
[TD="align: right"]05/04/04[/TD]
[TD="align: right"]48[/TD]
[TD]Female[/TD]
[TD]01/01/10[/TD]
[TD]01/03/13[/TD]
[TD]2 years, 2 months, 0 days[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]123 456 7890[/TD]
[TD="align: right"]06/04/85[/TD]
[TD="align: right"]50[/TD]
[TD]Female[/TD]
[TD]06/01/12[/TD]
[TD]12/12/12[/TD]
[TD]0 years, 11 months, 6 days[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]123 765 8976[/TD]
[TD="align: right"]07/04/00[/TD]
[TD="align: right"]25[/TD]
[TD]Female[/TD]
[TD]12/12/13[/TD]
[TD][/TD]
[TD]3 years, 4 months, 22 days[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]111 222 3333[/TD]
[TD="align: right"]08/04/99[/TD]
[TD="align: right"]13[/TD]
[TD]Female[/TD]
[TD]01/12/14[/TD]
[TD][/TD]
[TD]2 years, 5 months, 3 days[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]345 654 3214[/TD]
[TD="align: right"]09/04/82[/TD]
[TD="align: right"]32[/TD]
[TD]Male[/TD]
[TD]01/05/16[/TD]
[TD][/TD]
[TD]1 years, 0 months, 3 days[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]345 888 6590[/TD]
[TD="align: right"]10/04/77[/TD]
[TD="align: right"]17[/TD]
[TD]Male[/TD]
[TD]01/07/16[/TD]
[TD]07/08/16[/TD]
[TD]0 years, 1 months, 6 days[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]345 888 6590[/TD]
[TD="align: right"]11/04/62[/TD]
[TD="align: right"]18[/TD]
[TD]Male[/TD]
[TD]01/10/16[/TD]
[TD]15/10/16[/TD]
[TD]0 years, 0 months, 14 days[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]345 888 6590[/TD]
[TD="align: right"]11/04/77[/TD]
[TD="align: right"]35[/TD]
[TD]Male[/TD]
[TD]01/11/16[/TD]
[TD]12/12/16[/TD]
[TD]0 years, 6 months, 3 days[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]456 876 1111[/TD]
[TD="align: right"]22/05/64[/TD]
[TD="align: right"]40[/TD]
[TD]Male[/TD]
[TD]01/12/16[/TD]
[TD][/TD]
[TD]0 years, 5 months, 3 days[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]456 876 5436[/TD]
[TD="align: right"]07/04/68[/TD]
[TD="align: right"]55[/TD]
[TD]Male[/TD]
[TD]01/01/17[/TD]
[TD]12/01/17[/TD]
[TD]0 years, 0 months, 11 days[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]646 482 9002[/TD]
[TD="align: right"]11/04/77[/TD]
[TD="align: right"]40[/TD]
[TD]Male[/TD]
[TD]01/02/17[/TD]
[TD]25/02/17[/TD]
[TD]0 years, 0 months, 24 days[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]646 482 9002[/TD]
[TD="align: right"]22/05/64[/TD]
[TD="align: right"]52[/TD]
[TD]Male[/TD]
[TD]01/03/17[/TD]
[TD][/TD]
[TD]0 years, 2 months, 3 days[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]666 666 2323[/TD]
[TD="align: right"]07/04/68[/TD]
[TD="align: right"]49[/TD]
[TD]Female[/TD]
[TD]01/04/17[/TD]
[TD][/TD]
[TD]0 years, 1 months, 3 days[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]986 432 7890[/TD]
[TD="align: right"]12/12/12[/TD]
[TD="align: right"]104[/TD]
[TD]Male[/TD]
[TD]01/05/17[/TD]
[TD][/TD]
[TD]0 years, 0 months, 3 days[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]123 123 1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/01/17[/TD]
[TD]30/01/17[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]123 123 1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/02/17[/TD]
[TD]26/02/17[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]123 123 1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/03/17[/TD]
[TD]24/03/17[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]123 123 1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/04/17[/TD]
[TD]18/04/17[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]123 123 1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/05/17[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]345 888 6590[/TD]
[TD="align: right"]11/04/62[/TD]
[TD="align: right"]18[/TD]
[TD]Male[/TD]
[TD]02/05/17[/TD]
[TD][/TD]
[TD]0 years, 0 months, 14 days[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]123 456 7890[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/04/17[/TD]
[TD]18/04/17[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]123 456 7890[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30/04/17[/TD]
[TD]30/04/17[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]65 409 8111[/TD]
[TD="align: right"]05/04/04[/TD]
[TD="align: right"]48[/TD]
[TD]Female[/TD]
[TD]01/11/13[/TD]
[TD]01/03/14[/TD]
[TD]2 years, 2 months, 0 days[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]And now the results:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Readmission data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Readmissions in last 30 days[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Average readmissions in any 30 day period[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Readmissions in last 365 days[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Average readmissions in any 365 day period[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The results are currently just a sum of the relevant columns.
 
Last edited:
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Looking good...

As I said my data is in an excel table so countless rows can be added with various formatting, formulae etc follow whenever a line is added.

I've then got loads of very hidden sheets with pivots charts / tables...

There's one other sheet which forms a kind of dashboard with all manner of summary charts... I will be adding the 'results' on there with data being pulled through...

Will your configuration be OK when people start sorting data and reordering data??

Thx
 
Upvote 0
Currently at work on restricted network, so can't access dropbox!! Will have to have a look when I get home!! Thx...
 
Upvote 0
Perfect - all added to my workbook and functioning brilliantly - thank you so much...


Just one remaining (hopefully quick) question...


I'm displaying the results as, for example:


[TABLE="width: 423"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 4"]
Total readmissions in last 30 day period =​
[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="colspan: 4"]
Total readmissions in any given 30 day period =​
[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]
Total readmissions in last 365 days =​
[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="colspan: 4"]
Total readmissions in any given 365 day period =​
[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]


How do I add rows showing 'average readmissions in any 30 / 365 day period?
So basically 'Total Readmissions' divided by (number of 30/365 day intervals between [first admission recorded on sheet] and [today])

Thx again
 
Upvote 0
I amended the file in dropbox.

The formula for Ave in 30 day period is
=H44/COUNTIFS(H2:H30,">"&1)

H44 = Total readmissions in any 30 day period
H2:H30 = Admission Number column

Same for 365 day period, except change H44 to be the cell with
Total readmissions in any 365 day period
 
Upvote 0
Sorry to be a pain but wouldn't the 'total readmissions in any period' (i.e. since records began) always be higher than 'readmissions in last X days'...

So if there were 4 readmissions within the last thirty days (i.e. ending today) - then the total in any 30 days since recording began would surely be either 4, or more likely a greater number??
 
Upvote 0
Oops i think i made a mistake! Should just be a sum as per the last 30/365 days formula.

Hmm made a mistake on the ave% too!
 
Last edited:
Upvote 0
Think i got mixed up with the dif headers!

Try, see if it looks right

30 day ave
=SUM(L2:L30)/((TODAY()-MIN(E2:E30))/12)

365 day ave

=SUM(M2:M30)/((TODAY()-MIN(E2:E30))/365)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top