count number of dates within given time period

Hi all,

I am completely at a loss for how this can be done or even if it can be done at all....

I have attached a copy of a sheet with dummy data entered. I'm looking for 2 formulas to count the number of people who are seen within 3 weeks of referal and then either discharged within 6 weeks (formula 1) or given a perscription within 6 weeks and then go on to receive 4 or more follow appoinments within 1 year (formula 2).

Ideally formual 1 (Number seen within 3 weeks of referal and discharged within 6 weeks) would return 1 (person 3) and formual 2 (Number seen within 3 weeks, given prescription within 6 weeks and receive => 4 follow up appointments) would return 1 (person 1).

Many thanks in advance for any help,

Take out the N/A in columns D and E and you can use:

Formula:

=SUMPRODUCT(--($B$2:$B$13<>""),--($C$2:$C$13-$B$2:$B$13<=21),--($D$2:$D$13<>""),--($D$2:$D$13-$B$2:$B$13<=42))

to get the first value.


If you add a "Follow up" helper column in column G with the formula: G2 and copy down:

Formula:

=COUNTIF($A$2:$A$13,$A2)-1

... you can then use:

Formula:

=SUMPRODUCT(--($B$2:$B$13<>""),--($C$2:$C$13-$B$2:$B$13<=21),--($E$2:$E$13<>""),--($E$2:$E$13-$B$2:$B$13<=42),--($G$2:$G$13>=4))

to get the second value.


You haven't got a lot of data so you'll need to check it out but it looks as though it will work.


Regards, TMS

it doesn't seem to limit to counting 4 or more follow ups within a year though?

Well, you didn't say "within a year". It is counting occurrences of that patient ID, less 1, assuming the first visit doesn't count. Hence, is the helper column >= 4?

Unless you repeat the referral date in each patient record, I don't see how you will be able to produce the figure that you are looking for. If you do, you might be able to use a COUNTIFS function to calculate how many entries are within a given timeframe, for example, referral date + 365.

The separate elements of the formula would create a matrix of TRUE or FALSE entries. The -- coerces those boolean values int 1s and 0s which can then be multiplied together and added up.


Regards, TMS

Ah I realise now I wrote within a year in the text at the top but not further down in the example or in the spreadsheet. Sorry that was my bad.

You are quite right that the first value doesn't count. I think I'm nearly there by creating 2 more helper columns in H and I and altering the last part of the formula, but I need to alter what I'm doing in H as currently it is not doing a calculation which helps acheive the end goal

Thanks,
James

To quote:

count number of dates within given time period

and:

count the number of people who are seen within 3 weeks of referal and then either discharged within 6 weeks (formula 1) or given a perscription within 6 weeks and then go on to receive 4 or more follow appoinments within 1 year (formula 2).

3 weeks and 6 weeks are "time periods" as is "within 1 year" ... I saw the first but not the latter as I was focused on the requirement in the third paragraph. So, not entirely your fault However, as I said, you'd need to do something more to get the second formula.


The problem with the formulae you have is that you need to get that counter on the row with the original appointment.

Try changing I2 to:

Formula:

=COUNTIFS($A$2:$A$24,$A2,$H$2:$H$24,1)

and then this:

Formula:

=SUMPRODUCT(--($B$2:$B$24<>""),--($C$2:$C$24-$B$2:$B$24<=21),--($E$2:$E$24<>""),--($E$2:$E$24-$B$2:$B$24<=42),--($I$2:$I$24>=4))

should give you what you're looking for.

It looks as though column G is redundant ... I've deleted the contents to demonstrate but you should be able to delete the column.

Regards, TMS

One other thought. Be interesting to test what happens when a patient has another appointment sometime in the near future. I think it might get a bit messy then.

I haven't done anything with it but I think a way around it is to have appointment numbers. They could be unique to the individual or just unique, full stop. Whatever, I think you'll need to be able to isolate patient/appointment rather than just patient.


Regards, TMS