Excel Formula to Find the Next Admission Date After Discharge
Question details
Find the earliest admission date for each patient that falls on or after the patient's discharge date.

- 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.
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.
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.
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.
Select column D, then choose Home > Number Format > Short Date so the returned date is readable.
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.

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. Open the worksheet: In WPS Spreadsheets, select Open and choose your Excel workbook.
- 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. Format and fill: Format column D as a date, then drag the fill handle from D2 down to the last patient row.
- 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.

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.




