'%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)
How to Stop NETWORKDAYS.INTL When a Date Is Entered in Another Cell
Question details
The user wants to calculate ongoing working days dynamically, stopping the NETWORKDAYS.INTL calculation when a specific completion date is entered in a designated cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project durations or open ticket times where the working days count continuously up to yesterday, unless a task is marked complete with a final date.
- Observed behavior
- The standard NETWORKDAYS.INTL formula continues to count using a fixed end-date reference. The user needs it to toggle between a specific completion date and yesterday's date depending on whether the completion cell is blank.
Ensure that your designated start date and completion date cells are properly formatted as 'Date' to prevent calculation errors like #VALUE!.
Use a Nested IF Formula with NETWORKDAYS.INTL
Combine the IF function with NETWORKDAYS.INTL to dynamically change the end-date parameter based on whether a cell is blank.
By nesting NETWORKDAYS.INTL inside an IF statement, you can instruct your spreadsheet to evaluate whether a completion date exists. If a date is present, it calculates the working days between the start date and the completion date. If the cell is empty, it uses the TODAY() function to calculate the working days up to yesterday.
Click on the cell where you want the calculated total working days to be displayed.
Type the formula =IF(G48="","",IF(D48<>"",NETWORKDAYS.INTL(G48,D48),NETWORKDAYS.INTL(G48,TODAY()-1))) into the formula bar. Replace 'G48' with the cell reference for your start date, and 'D48' with the cell reference for your completion date.
Press Enter to execute the formula. If you are tracking multiple tasks, click and drag the fill handle at the bottom-right corner of the cell down to apply the logic to other rows.
Manage Complex Date Formulas in WPS Spreadsheet
WPS Spreadsheet fully supports advanced nested formulas, including IF, NETWORKDAYS.INTL, and TODAY(), allowing you to effortlessly build dynamic date trackers.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your date records.
- 2. Select the output cell: Click the specific cell where you want to display the dynamic working days count.
- 3. Input the nested formula: Enter the formula combining IF and NETWORKDAYS.INTL tailored to your start and end date cells.
- 4. Calculate and drag: Press Enter to instantly calculate the duration, then drag the fill handle to copy the formula to the rest of your dataset.
Frequently Asked Questions
Why does my NETWORKDAYS.INTL formula return a #VALUE! error?
This typically occurs if the referenced start or end cells contain text instead of recognized dates. Check your cells and verify they are formatted correctly as Dates.
Can I exclude custom holidays using this dynamic formula?
Yes. NETWORKDAYS.INTL allows you to reference a range of custom holiday dates. Add this range as the fourth argument in both instances of the function, for example: NETWORKDAYS.INTL(G48, D48, 1, Holidays!A2:A10).
How do I make the formula calculate up to today instead of yesterday?
To include today in the ongoing count, simply replace the 'TODAY()-1' segment in the final part of your formula with just 'TODAY()'.
What does the <> operator mean in this Excel formula?
The <> operator means 'not equal to'. In the expression IF(D48<>""), the formula is evaluating 'if cell D48 is not equal to blank' (meaning it contains data).
