'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Excel Formula to Stop Counting Days When a Date Is Entered
Question details
The user needs an Excel formula to calculate the number of remaining or overdue days, which stops updating once a completion date is entered.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project timelines or task durations where a live day counter needs to halt upon task completion.
- Observed behavior
- The current formula TODAY()-R14 continues to recalculate indefinitely based on the current date, regardless of whether a completion date has been entered.
Ensure that your start date and completion date cells are properly formatted as Dates in Excel, and that the cell where you type the formula is formatted as a Number or General so it displays the correct integer.
Use IF and ISBLANK to Calculate Final Duration
Use this approach if you want the formula to freeze and display the exact number of days it took to complete the task once the completion date is filled.
By combining the IF, ISBLANK, and TODAY functions, you can instruct Excel to check if the completion date cell is empty. If it is empty, the formula calculates the days up to today. Once a date is entered, it calculates the days between the start date and the completion date.
Click on the cell where you want the counted days to be displayed (for example, S14).
Type the formula =IF(ISBLANK(Y14), TODAY()-R14, Y14-R14). In this formula, R14 is your start date and Y14 is your completion date.
Press Enter. The cell will now dynamically show the ongoing day count or the final locked day count depending on the content of cell Y14.
Return a Blank Cell Upon Task Completion
Use this alternative formula if you no longer need to display any duration once the task is marked as finished.
Easily Manage Date Formulas with WPS Office
WPS Office Spreadsheet offers full support for advanced date and time functions, including IF, ISBLANK, and TODAY. You can seamlessly track your project timelines, format dates, and automate task management.
- 1. Open your worksheet: Launch WPS Spreadsheet and open your project tracker file.
- 2. Select the formula cell: Click on the cell designated for the day counter.
- 3. Input the logic: Enter =IF(ISBLANK(Y14), TODAY()-R14, Y14-R14) and press Enter.
- 4. Apply to multiple tasks: Drag the fill handle at the bottom-right corner of the cell downwards to apply this logic to all other rows.
Frequently Asked Questions
Why does my date formula return a 5-digit number instead of the number of days?
This happens when the cell containing your formula is incorrectly formatted as a Date instead of a Number. Excel processes dates as sequential numbers. To fix this, right-click the cell, select 'Format Cells', and change the category to 'General' or 'Number'.
Can I stop the formula from counting weekends?
Yes. Instead of standard subtraction, you can use the NETWORKDAYS function to exclude weekends. For example, use =IF(ISBLANK(Y14), NETWORKDAYS(R14, TODAY()), NETWORKDAYS(R14, Y14)).
What if my completion date cell contains a formula returning a blank string?
The ISBLANK function evaluates to FALSE if the referenced cell contains a formula, even if that formula returns a blank value (""). In this case, you should use the equals operator instead: =IF(Y14="", TODAY()-R14, Y14-R14).
