Formula Errors

Fix Excel NETWORKDAYS Formula Returning Negative Results

Tauseeq MagsiTauseeq Magsi Oct 12, 2026 870 views

Question details

Resolving an issue where copying the NETWORKDAYS formula to other rows produces negative workday counts.

Fix Excel NETWORKDAYS Formula Returning Negative Results
Product
Spreadsheet
Device & OS
not provided
Scenario
Calculating the number of working days across multiple rows by dragging down a NETWORKDAYS formula to copy it.
Observed behavior
The formula returns incorrect or negative results because the unlocked date references shift downwards into empty or incorrect cells.
Before you start

Double-click the cell returning a negative result to highlight the referenced cells. Check if the start date, end date, or holiday range has accidentally shifted down from its intended position.

Solution 1Recommended

Lock Fixed Date References with Absolute Referencing

Use absolute cell references to freeze specific dates so they do not shift when you copy the formula down a column.

In spreadsheet applications, dragging a formula down a column uses relative referencing by default. This means the row numbers increase automatically. If your NETWORKDAYS formula refers to a static start date, a fixed end date, or a set holiday list, you must lock those specific cells with dollar signs ($) to prevent negative or incorrect workday counts.

1
Select the cell with the formula

Click on the first cell containing your original NETWORKDAYS formula where the result is still correct.

2
Highlight the reference to lock

In the top formula bar, click on or highlight the cell reference that should not change, such as a fixed start date or a specific holiday list range (e.g., E2).

3
Apply absolute referencing

Press the F4 key on your keyboard. This instantly adds dollar signs to the column letter and row number (e.g., changing E2 to $E$2), locking the reference in place.

4
Copy the updated formula

Press Enter to save your changes. Click the small square fill handle in the bottom-right corner of the selected cell and drag it down to accurately apply the formula to the remaining rows.

Lock Fixed Date References with Absolute Referencing
F4 Key Shortcut: Pressing F4 repeatedly toggles between absolute, mixed, and relative references (e.g., $E$2, E$2, $E2, E2). Ensure both the row and column have a dollar sign to fully lock a single cell.

Calculate Working Days Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports the NETWORKDAYS function, absolute cell referencing, and over 400 other advanced formulas. You can manage complex data and accurately calculate business days with a familiar, easy-to-use interface.

  1. 1. Open your workbook: Launch WPS Office and open your spreadsheet document containing the dates.
  2. 2. Enter the NETWORKDAYS function: Select an empty cell and type =NETWORKDAYS(start_date, end_date, [holidays]).
  3. 3. Lock the references: Highlight any fixed date references in the formula bar and press F4 to insert absolute referencing dollar signs.
  4. 4. Drag to fill: Drag the green fill handle downward to calculate workdays for the rest of your dataset perfectly.
Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Native support for the NETWORKDAYS function and F4 absolute reference shortcuts.Free, lightweight, and fast alternative to heavy spreadsheet software.
microsoft office alternative - wps office

Frequently Asked Questions

What does the dollar sign ($) do in a spreadsheet formula?

The dollar sign creates an absolute reference. It locks the column letter, row number, or both so they do not shift or change when the formula is copied or dragged to another cell.

Why do I need to lock the holiday range in the NETWORKDAYS formula?

If you don't lock the holiday range (for example, making it $F$2:$F$10), dragging the formula down will cause the holiday reference to shift down to F3:F11. This means some of your listed holidays will be missed in the calculation, leading to incorrect day counts.

Does the NETWORKDAYS function automatically exclude weekends?

Yes, the NETWORKDAYS function automatically excludes standard weekends (Saturday and Sunday) from the total count of days between the start date and end date. You only need to provide a list of holidays if you want specific custom dates excluded as well.