Excel Formula to Stop Counting Days on the Discharge Date
Question details
The user needs an Excel/WPS formula that calculates length of stay from a Date of Entry through today, but stops counting on the Discharge Date once one is entered.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Tracking patient or client length of stay in a table with Date of Entry and Discharge Date columns.
- Observed behavior
- The basic formula continues counting through today after a discharge date is entered, producing inflated values such as 112 instead of 11.
Open your WPS Office spreadsheet and confirm the data is formatted as an Excel Table with Date of Entry and Discharge Date columns. Ensure both date columns use Date formatting and blank discharge cells are truly empty.
Use MIN with TODAY to Stop Counting on the Discharge Date
Use this when you need length of stay to end at the discharge date if present, otherwise continue counting through today.
The formula returns the smaller of the Discharge Date and TODAY(), then subtracts the Date of Entry. When Discharge Date is blank, MIN returns TODAY(); when a discharge date is entered, MIN returns that date.
Click the first cell in the Length of Stay column beside a data row, such as B2.
Type =IF([@[Date of Entry]]="","",MIN([@[Discharge Date]],TODAY())-[@[Date of Entry]]) in the formula bar and press Enter.
If the data is formatted as an Excel Table, the formula fills down automatically. Otherwise drag the fill handle down through the remaining rows.
Confirm rows with a blank Discharge Date continue counting through today, and rows with a Discharge Date stop on that date.

Calculate Length of Stay in WPS Spreadsheets
You can do this directly in WPS Office Spreadsheets with the same MIN and TODAY formula. WPS supports Excel structured references and date functions, so the calculation behaves the same as in Microsoft Excel.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the .xlsx workbook that contains the Date of Entry, Length of Stay, and Discharge Date columns.
- 2. Click the Length of Stay cell: Select the first result cell in the Length of Stay column where the calculation should appear.
- 3. Enter the date formula: Type =IF([@[Date of Entry]]="","",MIN([@[Discharge Date]],TODAY())-[@[Date of Entry]]) and press Enter.
- 4. Save the file: Press Ctrl+S to save the workbook; the formula remains compatible with Excel .xlsx format.

Frequently Asked Questions
Why does =TODAY()-Date_of_Entry keep counting after a discharge date?
That formula always uses the current date. Replace it with =IF([@[Date of Entry]]="","",MIN([@[Discharge Date]],TODAY())-[@[Date of Entry]]) so the calculation uses Discharge Date whenever it is present.
How can I show a blank result instead of 0 when the Date of Entry is empty?
The IF condition [@[Date of Entry]]="" returns an empty string, so the cell remains blank until an entry date is entered.
Does this formula work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports Excel structured references, MIN, TODAY, and IF, and can open and save .xlsx files without changing the formula.
What if my data is not formatted as an Excel Table?
Use regular cell references, such as =IF(A2="","",MIN(C2,TODAY())-A2), and copy the formula down to the other rows.
How can I display the length of stay in whole days?
Subtracting two date cells returns a serial number. Format the result cell as Number with 0 decimal places to display whole days.




