Function Problems

Find the Next Admission Date After Discharge in Excel

Camila MilosovichCamila Milosovich Nov 18, 2026 868 views

Question details

Find the earliest admission date for each patient that is strictly later than the discharge date.

Find the Next Admission Date After Discharge in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains patient IDs, admission dates, and discharge dates, and the user needs the next qualifying admission for each row.
Observed behavior
The desired result is the earliest future admission for the same patient, excluding dates on or before discharge.
Before you start

Confirm that patient IDs are consistent and that admission and discharge entries are valid spreadsheet dates, not text. The example uses IDs in A2:A8, admission dates in B2:B8, and discharge dates in C2:C8.

Solution 1Recommended

Use MINIFS to Find the Next Admission for Each Patient

Match the patient ID and return the earliest admission strictly after the discharge date, leaving the cell blank if there is no match.

COUNTIFS checks whether a future admission exists before MINIFS returns the earliest matching date. This prevents a no-match result from appearing as a zero date.

1
Enter the formula

Select D2 and enter =IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,">"&C2)=0,"",MINIFS($B$2:$B$8,$A$2:$A$8,A2,$B$2:$B$8,">"&C2)).

2
Format the result

Select column D and choose a date format from Home > Number Format so matching results appear as dates.

3
Fill down

Drag the fill handle from D2 down through the remaining patient rows. Adjust the fixed ranges if your data extends beyond row 8.

Use MINIFS to Find the Next Admission for Each Patient
Include discharge-day admissions: If an admission on the discharge date should qualify, replace both instances of ">" with ">=" in the formula.
Work with patient dates in WPS

Find the Next Patient Admission in WPS Spreadsheet

Open your workbook in WPS Spreadsheet and use the same COUNTIFS and MINIFS formula to identify each patient's next admission after discharge.

  1. 1. Open the workbook: In WPS Spreadsheet, choose Open and select the workbook containing the patient data.
  2. 2. Enter the formula: Select D2 and enter =IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,">"&C2)=0,"",MINIFS($B$2:$B$8,$A$2:$A$8,A2,$B$2:$B$8,">"&C2)).
  3. 3. Format and fill: Set column D to a date format using Home > Number Format, then drag D2's fill handle down for the remaining rows.
  4. 4. Save the workbook: Choose File > Save As and select XLSX if you want to keep the workbook in Excel format.
Find the earliest future admission for a matching patient IDLeave the result blank when no future admission existsOpen and save Microsoft Excel XLSX workbooksUse familiar spreadsheet formulas and date formatting
microsoft office alternative - wps office

Frequently Asked Questions

Why does MINIFS return a zero date when there is no future admission?

MINIFS returns zero when no cells meet its criteria. Use COUNTIFS to check for a matching future admission first, as in the formula above, and return an empty string when the count is zero.

How can I include an admission on the discharge date?

Replace ">" with ">=" in both the COUNTIFS and MINIFS criteria. This includes admissions on the discharge date as well as later ones.

Why does the result appear as a number instead of a date?

Excel stores dates as serial numbers. Select the result cells and choose a date format under Home > Number Format.

What if my patient data extends beyond row 8?

Change every fixed range in the formula to cover all data rows, such as $A$2:$A$100 and $B$2:$B$100. Keep the ranges the same size.