Calculation Issues

How to Stop a Days-Engaged Count at the End Date in Excel

Muhammad TalhaMuhammad Talha Nov 10, 2026 869 views

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.

How to Stop a Days-Engaged Count at the End Date in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on the empty cell where you want the total days-engaged count to be displayed.

2
Enter the IF formula

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`.

3
Format the result as a number

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 IF and TODAY Functions to Calculate Elapsed Days
Dynamic Updates: The TODAY() function automatically updates every time you open or recalculate the workbook, keeping your ongoing days-engaged counts perfectly accurate.
Efficient Date Calculations in WPS Spreadsheet

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. 1. Open your dataset in WPS: Launch WPS Spreadsheet and open your workbook containing the start and end date columns.
  2. 2. Apply the date formula: Select the duration cell and input `=IF(B2="", TODAY(), B2) - A2` to calculate the elapsed days.
  3. 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.
100% compatible with Microsoft Excel (.xlsx) date formulas, functions, and formatting.Lightweight software with lightning-fast calculation speeds for large timeline datasets.Built-in formatting tools to easily switch between date formats and numeric day counts.Completely free to use with a clean, familiar tabbed user interface.
microsoft office alternative - wps office

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.