#VALUE! in NETWORKDAYS: Invalid Date formats

The #VALUE! error in NETWORKDAYS (and NETWORKDAYS.INTL) occurs when Excel cannot interpret one or more supplied arguments as a valid date serial number. Excel stores dates as sequential integers (where Day 1 is January 1, 1900). If the start_date, end_date, or any entry in the optional [holidays] argument contains text masquerading as a date, trailing spaces, an invalid date string, or a regional delimiter mismatch, the function halts calculation and returns #VALUE!.

Quick Fix

Convert text-based dates into true serial numbers using the DATEVALUE() function or re-enter them using =DATE(year, month, day). For dates already in cells A2 and B2, write =NETWORKDAYS(DATEVALUE(A2), DATEVALUE(B2)). If the cells contain valid dates that Excel fails to recognize due to regional settings, highlight the column, open Data > Text to Columns, and select Date at Step 3 to convert the entire range in bulk.

---

How Excel Dates Break NETWORKDAYS

The syntax for the function requires valid numeric serial dates:

=NETWORKDAYS(start_date, end_date, [holidays])

If you hardcode dates into the formula as text strings—such as =NETWORKDAYS("01/05/2024", "01/20/2024")—the formula may work on your computer but fail on a colleague’s system due to Windows regional settings (e.g., US m/d/yyyy versus UK d/m/yyyy). When Excel cannot reconcile the format with your operating system’s local calendar settings, it treats the date as a non-numeric text string, triggering a #VALUE! error as seen in #VALUE! in Subtraction: Dates stored as Text.

---

Diagnostic Procedure: Finding the Bad Date

When troubleshooting a worksheet where cells look like regular dates, follow these diagnostic steps to identify the invalid entry.

Step 1: Run the ISNUMBER Test

Real dates in Excel are numbers formatted visually as dates:

1. In an adjacent blank column, enter =ISNUMBER(A2) (pointing to your start_date).
2. Do the same for your end_date and each cell in your [holidays] list.
3. If any cell returns FALSE, that cell is stored as text, not a date.

Step 2: Check the Holidays Range

A frequent cause of #VALUE! in NETWORKDAYS is an invalid entry hidden inside the [holidays] array:

• If your formula is =NETWORKDAYS(A2, B2, H2:H20), and cell H15 contains a text note (such as "TBD", "Christmas (Observed)", or a blank cell populated by an empty text string "" from an upstream formula), NETWORKDAYS attempts to evaluate that text as a date and throws #VALUE!.
• Fix: Ensure the holiday range contains strictly valid date serial numbers or completely blank, unformatted cells.

Step 3: Check for Hidden Characters and Apostrophes

Text exports from ERP systems (SAP, Oracle, NetSuite) often wrap dates in non-printing ASCII characters or prepend them with an apostrophe ('2024-05-01):

• Check the Formula Bar: If an apostrophe appears before the date, Excel treats the contents as text.
• Check character length: In an empty cell, type =LEN(A2). A standard date formatted as YYYY-MM-DD should have 10 characters. If LEN returns 11 or higher, the cell contains invisible spaces or trailing carriage returns.

---

How to Fix Invalid Date Formats

Method 1: The “Text to Columns” Bulk Conversion (Fastest)

When imported dates are stored as text across entire columns:

1. Select the column containing the problematic dates.
2. Go to the Data tab on the Ribbon and click Text to Columns.
3. Select Delimited and click Next.
4. Uncheck all delimiter boxes and click Next.
5. In Step 3, choose the Date radio button under Column data format.
6. Select the format that matches how the text is written in the cell (e.g., MDY for month-first, DMY for day-first, or YMD).
7. Click Finish.

Excel will convert the text strings into genuine date serial numbers, resolving the #VALUE! error immediately.

Method 2: Formula-Based Normalization

If your source data is linked dynamically and cannot be converted manually via menus, build date coercion directly into your formula:

• Using DATEVALUE: =NETWORKDAYS(DATEVALUE(TRIM(A2)), DATEVALUE(TRIM(B2)), HolidaysRange)
• Using DATE with MID/LEFT/RIGHT (For unstandardized strings like “20240501”): =NETWORKDAYS( DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)), DATE(LEFT(B2,4), MID(B2,5,2), RIGHT(B2,2)), HolidaysRange )

Method 3: Clean the Holiday Reference

If the holidays parameter references a dynamic table that might return blank text strings (""), filter the range so only numbers pass through:

=NETWORKDAYS(A2, B2, FILTER(H2:H20, ISNUMBER(H2:H20)))

This formula strips out column headers, descriptive text labels, and empty formula strings before passing the remaining dates into NETWORKDAYS.

For projects operating across multiple regional or international work calendars, review your setup against Workday Calculation: Fixing NETWORKDAYS when it fails to account for regional bank holidays. to ensure custom non-working days do not corrupt output values.

For wider troubleshooting across logical arguments and formula tracing, see Fixing #VALUE!, #DIV/0!, and #NUM! Errors: The Excel Data Logic Guide and Using the “Evaluate Formula” tool to trace the root of #VALUE!.