How to Stop a Days-Engaged Count at the End Date in Excel
Question details
The user needs to calculate the number of elapsed days from a start date, stopping exactly at a specified end date. If no end date has been entered yet, the calculation should dynamically continue counting up to the current date.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project durations, task completion times, or employee engagement periods where an end date might still be pending.
- Observed behavior
- The calculation must dynamically switch between subtracting from a specific end date or from today's date depending on whether the end date cell is blank.
Ensure that your Start Date and End Date columns contain valid Excel date values rather than text strings, and prepare to format your result column as a standard Number.
Use the IF and TODAY Functions to Calculate Elapsed Days
This is the most reliable method. It uses the IF function to dynamically check if the end date is blank and calculates the days accordingly without breaking when empty cells are encountered.
By nesting the TODAY() function inside an IF statement, you tell Excel to use the current date whenever the End Date cell is empty. If an End Date exists, Excel uses that specific date instead.
Click on the empty cell where you want the total days-engaged count to be displayed.
Type the formula `=IF([@[End Date]]="", TODAY(), [@[End Date]]) - [@[Start Date]]` into the formula bar. If you are working with a standard range instead of an Excel Table, replace the bracketed references with standard cell references, for example: `=IF(B2="", TODAY(), B2) - A2`.
Press Enter to apply the formula. Because you are subtracting dates, Excel might format the result as a date. Right-click the cell, select 'Format Cells', choose 'Number', and set decimal places to 0 to view the count as whole days.

Use the MIN and TODAY Functions for a Shorter Formula
This alternative approach compares the End Date and Today's date, returning whichever is earlier, resulting in a slightly shorter formula.
Calculate Date Differences Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time functions, including IF, TODAY, and MIN, allowing you to seamlessly track project days and timelines. It's a highly compatible, free alternative to Microsoft Excel.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open your workbook containing the start and end date columns.
- 2. Apply the date formula: Select the duration cell and input `=IF(B2="", TODAY(), B2) - A2` to calculate the elapsed days.
- 3. Drag to fill the column: Click and drag the small fill handle at the bottom-right corner of the selected cell downwards to apply the formula to your entire dataset.

Frequently Asked Questions
Why is my formula returning a strange date instead of the number of days?
Excel automatically inherits the formatting of the referenced cells. Since you are calculating based on dates, it assumes you want the result formatted as a date. Simply right-click the cell, choose 'Format Cells', and change the format category from 'Date' to 'Number' or 'General'.
What happens to the calculation if the start date is in the future?
If the start date is later than the end date (or today's date), the formula will return a negative number. If you want to prevent negative days from displaying, you can wrap your formula in the MAX function like this: `=MAX(0, IF(B2="", TODAY(), B2) - A2)`.
Can I exclude weekends from the days-engaged count?
Yes. If you only want to calculate working days (Monday through Friday), use the NETWORKDAYS function instead of simple subtraction. The formula would look like this: `=NETWORKDAYS(A2, IF(B2="", TODAY(), B2))`.
Why does the MIN formula return a massive negative number when the end date is blank?
In Excel, a blank cell evaluated in a math function like MIN often defaults to 0, which corresponds to the date January 0, 1900. Because 1900 is earlier than today, MIN returns 0. Subtracting the start date from 0 results in a large negative number. Using the IF formula method avoids this issue entirely.




