Function Problems

Excel Formula to Find the Next Admission Date After Discharge

Ayan MasoodAyan Masood Nov 18, 2026 868 views

Question details

Find the earliest admission date for each patient that falls on or after the patient's discharge date.

Find the Next Admission Date After Discharge in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains patient index numbers, admission dates, and discharge dates, and needs a next admission date for each row.
Observed behavior
The result should match the patient index number, ignore earlier admissions, and return the earliest qualifying date.
Before you start

Check that patient IDs are consistent and that admission and discharge dates are stored as actual spreadsheet dates, not text. The examples assume IDs are in A2:A9, admission dates in B2:B9, and discharge dates in C2:C9.

Solution 1Recommended

Use MINIFS to find the earliest qualifying admission

Use this formula in a compatible Excel version to match the patient ID and return the first admission on or after discharge.

1
Enter the formula

Select D2 and enter =MINIFS($B$2:$B$9,$A$2:$A$9,A2,$B$2:$B$9,">="&C2). Adjust the ranges if your data extends beyond row 9.

2
Format the result

Select column D, then choose Home > Number Format > Short Date so the returned date is readable.

3
Fill down

Drag the fill handle from D2 down through the remaining patient rows. The fixed ranges stay in place while the patient ID and discharge date change for each row.

Use MINIFS to find the earliest qualifying admission
Same-day admissions and missing matches: The >= condition includes an admission on the discharge date; use > instead for a strictly later admission. MINIFS returns zero when no date matches. To show a blank instead, use =IF(COUNTIFS($A$2:$A$9,A2,$B$2:$B$9,">="&C2)=0,"",MINIFS($B$2:$B$9,$A$2:$A$9,A2,$B$2:$B$9,">="&C2)).
WPS Spreadsheets

Find the Next Patient Admission in WPS Spreadsheets

Open your patient worksheet in WPS Spreadsheets and use MINIFS to find the earliest admission on or after each discharge date.

  1. 1. Open the worksheet: In WPS Spreadsheets, select Open and choose your Excel workbook.
  2. 2. Enter the formula: Select D2 and enter =MINIFS($B$2:$B$9,$A$2:$A$9,A2,$B$2:$B$9,">="&C2), adjusting the ranges to fit your data.
  3. 3. Format and fill: Format column D as a date, then drag the fill handle from D2 down to the last patient row.
  4. 4. Save the workbook: Select Save or Save As and keep the workbook in XLSX format if you need to share it with Excel users.
Match patient IDs and filter admissions by discharge dateOpen and edit Microsoft Excel XLSX filesFill the formula down for multiple patient recordsUse familiar spreadsheet formatting and formula tools
microsoft office alternative - wps office

Frequently Asked Questions

How do I exclude an admission on the discharge date?

In the formula, replace ">="&C2 with ">"&C2. This returns only an admission strictly after the discharge date.

Why does the formula return a zero or an unexpected date?

MINIFS returns zero if no admission meets both conditions, and date formatting can display that zero as a date. Use the COUNTIFS and IF version in the solution to show a blank when there is no match.

What if the formula does not recognize my dates?

Check columns B and C for dates stored as text. Convert them to actual dates, for example with Excel's Data > Text to Columns, before using the formula.

What does the formula do if a patient has several qualifying admissions?

MINIFS checks all rows in the specified ranges and returns the earliest admission date that matches the patient ID and date condition.

Does MINIFS work in every version of Excel?

No. MINIFS requires a compatible Excel version. If Excel does not recognize the function, check your version or open the workbook in a spreadsheet app that supports MINIFS.