How to Return Blank for Negative Values Using NETWORKDAYS in Excel
Question details
The user needs an Excel formula to calculate the number of working days between two dates while ensuring that any negative date differences return a blank cell instead of a negative number.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the duration of tasks or projects in working days where some end dates might be mistakenly earlier than start dates or missing, leading to negative results.
- Observed behavior
- The user wants to suppress negative day counts generated by the NETWORKDAYS function and display an empty string instead.
Ensure that your start and end date cells are formatted as dates rather than text to prevent the NETWORKDAYS function from returning a #VALUE! error.
Combine the IF Function with NETWORKDAYS
Use an IF statement to evaluate whether the end date is earlier than the start date. If it is, return a blank cell; otherwise, run the NETWORKDAYS function.
The NETWORKDAYS function natively returns a negative number if the start date is later than the end date. By performing a simple subtraction within an IF function first, you can intercept these negative scenarios before the days are calculated.
Click on the cell where you want the calculated working days to appear (for example, row 171).
Type the formula: =IF(I171-A171<0, "", NETWORKDAYS(A171, I171)). In this example, A171 represents the Start Date and I171 represents the End Date.
Press Enter to see the result. If the end date is earlier, the cell will be blank. Click and drag the fill handle down to apply the formula to the rest of your data.
Evaluate the NETWORKDAYS Output Directly
Instead of subtracting the dates beforehand, you can test the output of the NETWORKDAYS function itself to see if it drops below zero.
Calculate Dates Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides robust support for all standard date formulas, including IF and NETWORKDAYS. You can manage project timelines, handle formula errors, and organize your schedules with ease in a familiar, user-friendly interface.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing project tracker or date log.
- 2. Input the formula: Select the duration column and type your =IF() and NETWORKDAYS() combination.
- 3. Use auto-fill: Double-click the small square at the bottom right of the active cell to automatically fill the formula down for all your records.
Frequently Asked Questions
Why does NETWORKDAYS return a negative number?
NETWORKDAYS calculates the difference between a start date and an end date. If the start date provided in the formula is chronologically later than the end date, the function interprets this as moving backward in time, resulting in a negative count of working days.
Why is my formula returning a #NAME? error?
A #NAME? error usually occurs if there is a typo in the function name (such as typing NETWROKDAYS instead of NETWORKDAYS) or if you forgot to include the quotation marks around the blank string ("") in your IF statement.
Can I return a zero instead of a blank cell?
Yes. To return a zero instead of a blank cell, simply replace the empty quotation marks in the IF formula with a 0. The formula would be: =IF(I171-A171<0, 0, NETWORKDAYS(A171, I171)).
How do I calculate total days including weekends?
If you want to include weekends in your calculation, you do not need the NETWORKDAYS function. You can just subtract the start date from the end date using =IF(End_Date-Start_Date<0, "", End_Date-Start_Date).
