Calculation Issues

Excel Formula to Stop Counting Days on the Discharge Date

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 868 views

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.

Excel Formula to Stop Counting Days on the Discharge Date
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the Length of Stay cell

Click the first cell in the Length of Stay column beside a data row, such as B2.

2
Enter the formula

Type =IF([@[Date of Entry]]="","",MIN([@[Discharge Date]],TODAY())-[@[Date of Entry]]) in the formula bar and press Enter.

3
Verify auto-fill or copy down

If the data is formatted as an Excel Table, the formula fills down automatically. Otherwise drag the fill handle down through the remaining rows.

4
Check blank discharge dates

Confirm rows with a blank Discharge Date continue counting through today, and rows with a Discharge Date stop on that date.

Use MIN with TODAY to Stop Counting on the Discharge Date
Formula result verified: With 5/8/2023 as entry and 5/19/2023 as discharge, the formula returns 11 instead of 112.

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. 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. 2. Click the Length of Stay cell: Select the first result cell in the Length of Stay column where the calculation should appear.
  3. 3. Enter the date formula: Type =IF([@[Date of Entry]]="","",MIN([@[Discharge Date]],TODAY())-[@[Date of Entry]]) and press Enter.
  4. 4. Save the file: Press Ctrl+S to save the workbook; the formula remains compatible with Excel .xlsx format.
Stops the day count automatically when a Discharge Date is enteredFull compatibility with Excel .xlsx files and Excel formulasFree and lightweight alternative to Microsoft ExcelFamiliar ribbon interface with date functions such as MIN and TODAY
microsoft office alternative - wps office

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.