Formula for duplicate referral - same condition or repeat within 30-90 days

Kia ora All

I wish to create a spreadsheet which has formulas which would enable me to see if a patient has had more than one referral created within the next 30-90 days [the actual number of days not important - the ability to modify the formula to reflect which time period required would be cool] and also to see if a patient has been referred for the same medical condition again. Thus in my mind at least two different formulas - has the patient been referred again within a time period of days and has the patient been referred again for the same medical condition.

Attached is an example referral spreadsheet I have created

I thought that was an orange drink !!

You need to fix your dates in column D - highlight the column, click on Data | Text-to-Columns then click Finish. Then you can have this formula in J2:

=IF(COUNTIFS(A$2:A2,A2,D$2:D2,">"&D2-30,D$2:D2,"<="&D2)>1,"Yes","")

which looks back at 30 days (change the value in red for different periods), and this formula in K2:

=IF(COUNTIFS(E$2:E2,E2,A$2:A2,A2)>1,"Yes","")

Copy the formulae down the table.

Hope this helps.

Pete

Thanks Pete - solved my problem

Glad to hear it.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Thanks Pete

For the answer and the how to tidy up the closing and reputation adding stuff. Have completed

Thanks