How to find overdue patients in your schedule data

What to export

Almost every EHR and practice management system can export appointments to a spreadsheet file. Ask for an export covering at least the last 24 months and the next 12 months, so you can see both past visits and anything already booked. The columns you need are:

• Patient ID (account or chart number), the single most important column.
• Appointment date.
• Appointment type or visit code, such as cleaning, annual exam, follow-up or treatment visit.
• Status: completed, cancelled, no-show, scheduled.
• Provider, useful for sorting the work later.
• Contact preference or consent flag, if your system records it.

A note on handling the file

A schedule export is protected health information. Keep it on a practice-owned, encrypted device. Don't email it to a personal account or upload it to a free online tool. If anyone outside the practice will process it, including a consultant or software vendor, they need to sign a Business Associate Agreement first. When you're done, delete working copies you no longer need.

Only include the columns the task requires. You don't need diagnoses, notes or insurance details to build an overdue list, so leave them out of the export entirely.

The method, step by step

1. Clean the status column. Keep a copy of all rows, but create a filtered view of completed visits only. A patient who cancelled in June was not seen in June.
2. Find each patient's last completed visit. In a pivot table, put Patient ID in rows and the maximum appointment date of completed visits in values. In a formula, MAXIFS on the patient ID does the same job.
3. Find each patient's next scheduled visit. Same approach, using the minimum date among rows with status "scheduled" and a date after today.
4. Attach an expected interval. Map the last visit type to how soon the patient should return, using a small lookup table the clinical team agrees on (for example, cleaning → 6 months, annual exam → 12 months). Where the interval was set individually, use the checkout notes or a recall date field if your system has one.
5. Calculate the due date. Last completed visit date plus the interval.
6. Flag overdue patients. A patient is overdue if the due date is before today and the next scheduled visit is blank.
7. Calculate days overdue (today minus due date) and sort or bucket by it.

A worked example

Here's how three fictional patients look after the steps above, assuming today is October 8 and a 6-month interval for routine visits.

Patient 10482: last completed visit January 15, due July 15, nothing scheduled. Overdue by 85 days.

Patient 10517: last completed visit February 2, due August 2, but has a visit booked for November 4. Not overdue; already on the books.

Patient 10633: had a visit scheduled April 10 that was a no-show, and a completed visit the previous October 20. Their last completed visit is October 20, so their due date was April 20. Overdue by 171 days. If you had counted the no-show as their last visit, they would wrongly look current until October.

That third case is the most common error in homemade overdue lists, and it hides exactly the patients who most need a call.

Common traps

• Matching on name. "Jon Smith" and "Jonathan Smith" become two patients. Always match on the patient ID.
• Merged or duplicate charts. If your system has duplicate records for one person, one may look overdue while the other has a future booking. Spot-check the top of the list.
• Treatment plans with an end date. A patient who completed a finite plan of care isn't overdue for another one. Use a visit type mapping that reflects that.
• Patients who left the practice. Exclude those marked as transferred, deceased, moved away or opted out of contact.
• Date formats. Exports sometimes mix text and true dates. Convert the column to real dates before doing any math.
• Export windows that are too short. If you only export six months, any patient last seen before that disappears from the list instead of showing as overdue.

Sorting the finished list into a work plan

A raw overdue list is just a long column of patient IDs. To make it usable, add three helper columns and sort on them.

First, a bucket column based on days overdue: 0-90, 91-365, 366-730 and over 730. Second, a reachable column: yes if the patient has a mobile number and has agreed to texts, otherwise no. Third, the provider from the last completed visit, so each clinician's patients can be offered that clinician's open times.

Then filter to the first bucket, reachable patients only, and work that slice first. It's usually the smallest group and the one most likely to book. Patients who aren't reachable by text go on a short call list for the front desk's quieter hours. The oldest buckets come last.

Turning the list into a routine

The first pass takes an afternoon. After that, rerun the same steps on a fixed day each week, with a fresh export. Save the lookup table and formulas in a template so the next person can repeat it.

Each week, look at three things: how many patients newly crossed their due date, how many on last week's list have since booked, and how many you couldn't reach. Those three numbers tell you whether your recall work is keeping up or falling behind.

If you'd rather not build and maintain the spreadsheet, the free leak audit runs this analysis on your schedule export under a signed Business Associate Agreement and shows you the result.