#VALUE! in NETWORKDAYS: Invalid Date formats

Excel returns a #VALUE! error in NETWORKDAYS or NETWORKDAYS.INTL when the start_date, end_date, or any entry in the optional [holidays] range cannot be parsed into a valid Excel date serial number. This occurs when date arguments are stored as text strings, contain invalid day/month combinations, use non-standard date separators, or conflict with system regional date settings.

Fast-Fix: The 45-Second Solution

Excel throws a #VALUE! error in NETWORKDAYS because the function requires real numeric date serial numbers rather than plain text strings. To fix this, wrap text dates using =NETWORKDAYS(DATEVALUE(A2), DATEVALUE(B2)) or hardcode dates safely with =NETWORKDAYS(DATE(2026, 5, 1), DATE(2026, 5, 31)). Alternatively, convert an entire column at once by selecting the dates, opening the Data tab, clicking Text to Columns, and hitting Finish to coerce strings into valid serial numbers.

Quick Risk Snapshot

• Severity Tier: Moderate (Breaks work-day counts, capacity plans, and project SLA schedules).
• Is it safe to ignore?: No. Downstream cost calculations, labor tracking, and billing formulas will inherit #VALUE! errors or report invalid zero balances.
• Most common cause: Dates imported from web applications or CSV exports stored as text (e.g., "2026.08.05" or "25/12/2026" on a US system setting).
• Rare/Serious cause: Non-numeric text values, column headers, or formula outputs like "" included in the optional [holidays] range.

Low Risk vs. High Risk

• If the error occurs in a local schedule or single project tracker: It is Low Risk. Fixing the text date formatting using Text to Columns or wrapping inputs in DATEVALUE() resolves the calculation immediately.
• If the error occurs in cross-border financial models or multi-region HR payroll pipelines: It is High Risk. Regional date mismatches (DD/MM/YYYY vs. MM/DD/YYYY) cause some rows to throw #VALUE! errors while silently swapping month and day values on other rows, corrupting workday totals without warning.

The Mechanics of the Break

Excel stores dates internally as positive serial integers, starting with 1 for January 1, 1900 (e.g., August 5, 2026 is stored as 46239). The NETWORKDAYS(start_date, end_date, [holidays]) function calculates the net working days between two serial numbers, automatically excluding weekends (Saturday and Sunday) and any serial numbers listed in the [holidays] array.

When you pass a date argument as a text string (such as "2026-05-15"), Excel attempts an automatic background conversion using the operating system’s active regional settings. If that string uses an unrecognized format (like "15.05.2026"), an impossible calendar date (like "31/02/2026"), or a foreign date layout (like a UK date "25/05/2026" opened on a US system expecting MM/DD/YYYY), the conversion fails. NETWORKDAYS receives string data instead of a numeric integer, halts execution, and outputs #VALUE!.

Excel’s date engine operates like an automated subway turnstile that only accepts official metal tokens (date serial numbers). A valid date stored as text is like a token painted onto cardboard, if the pattern happens to match perfectly, the sensor might let it pass, but if the dimensions or separators are off even slightly, the turnstile jams and locks the gate.

Formula Input	Input Cell Content	Local System Setting	Evaluation Outcome	Root Cause
=NETWORKDAYS(A2, B2)	A2 = "05/10/2026"	US (MM/DD/YYYY)	105 (Valid)	Coerced to May 10, 2026
=NETWORKDAYS(A2, B2)	A2 = "25/05/2026"	US (MM/DD/YYYY)	#VALUE!	Month 25 does not exist; treated as invalid text
=NETWORKDAYS(A2, B2)	A2 = "2026.05.15"	US (MM/DD/YYYY)	#VALUE!	Periods are not valid date separators in US locale
=NETWORKDAYS(A2, B2, C2:C10)	C2 = "Holiday"	Any Locale	#VALUE!	Text header included in the [holidays] range

Probability Breakdown

• Likely (55%): CSV, ERP, or database imports outputting dates as text strings with period separators (2026.08.05) or leading/trailing spaces.
• Possible (30%): Regional date format conflicts (DD/MM/YYYY vs. MM/DD/YYYY) when opening workbooks across international teams.
• Rare (15%): Selecting an extended [holidays] range that includes text labels, header rows, or empty text strings ("").

What Escalates the Risk

Global team collaboration and automated data pipelines escalate this issue significantly. When team members in different countries open the same shared workbook, Excel evaluates text dates against each user’s local operating system settings.

A date string like "04/05/2026" will evaluate as April 5 in the US, May 4 in the UK, and fail with #VALUE! if pushed into a strict date format. If your [holidays] reference covers an entire column (e.g., C:C) that includes a text header in row 1, NETWORKDAYS will evaluate every single row in the sheet as #VALUE!.

Consequence Timeline

• 24 Hours: SLA tracking sheets and milestone trackers render #VALUE! errors, delaying daily operational updates.
• 1 Week: Inconsistent date interpretation across international teams leads to distorted project timelines and miscalculated billable hours.
• 1 Month: Accumulated errors in work-day reporting distort monthly payroll runs, labor capacity planning, and resource allocation models.

Common Confusion Fix

Distinguishing date format errors in NETWORKDAYS from other date-related formula breaks ensures an accurate fix:

• NETWORKDAYS #VALUE! vs. DATEDIF #NUM!: NETWORKDAYS returns #VALUE! when a date argument is an invalid text string. DATEDIF returns #NUM! when the start_date is chronologically later than the end_date.
• NETWORKDAYS #VALUE! vs. Subtraction #VALUE!: Simple cell subtraction (e.g., =B2-A2) might auto-coerce certain text date formats that NETWORKDAYS strictly rejects. For details on fixing general date subtraction issues, see #VALUE! in Subtraction: Dates stored as Text.
• NETWORKDAYS #VALUE! vs. NETWORKDAYS.INTL #VALUE!: Both functions return #VALUE! for invalid dates, but NETWORKDAYS.INTL will also return #VALUE! if the weekend string argument contains invalid characters or incorrect string lengths (such as supplying 6 digits instead of 7).

What To Do Right Now

1. Convert Text Dates to Serial Numbers in Bulk

1. Highlight the column containing the problematic dates.
2. Navigate to Data > Text to Columns.
3. Select Delimited and click Next.
4. Uncheck all delimiters and click Next.
5. Under Column data format, select Date and choose the exact order used in the raw text (e.g., YMD for 2026.05.15 or DMY for 25/05/2026).
6. Click Finish.

2. Wrap Mismatched Text Entries in Formula Converters

If you cannot alter the raw data column, convert the text strings inside the formula:

=NETWORKDAYS(DATEVALUE(A2), DATEVALUE(B2), C2:C10)

If the date uses custom separators like periods (2026.05.15), replace them dynamically:

=NETWORKDAYS(DATEVALUE(SUBSTITUTE(A2, ".", "/")), DATEVALUE(SUBSTITUTE(B2, ".", "/")))

3. Build Explicit Date Arguments

For fixed start or end dates, eliminate text parsing entirely by using the DATE function:

=NETWORKDAYS(DATE(2026, 5, 1), DATE(2026, 5, 31), C2:C10)

4. Sanitize the Holiday Range

Ensure the [holidays] argument references purely numeric dates. If your holiday list contains text headers, adjust the range to exclude row 1 (e.g., use C2:C10 instead of C1:C10 or C:C).

To trace where a nested calculation breaks, see Using the “Evaluate Formula” tool to trace the root of #VALUE!.

Hard-Stop Triggers

Stop entering data and inspect your system configuration if you observe any of these warning signs:

• Dates automatically flipping days and months (e.g., May 4 turning into April 5) when opened by users in different regional offices.
• =ISNUMBER(A2) returning FALSE on cells formatted visually as dates.
• NETWORKDAYS formulas breaking sheet-wide after linking a new holiday table range.

Professional Audit Path

To audit date integrity across a production workbook:

1. Verify Numeric Underlying Data: Insert an audit column with =ISNUMBER(cell) next to your start dates, end dates, and holiday lists. Any FALSE indicates a text string that will break NETWORKDAYS.
2. Scan for Hidden Characters: Use =LEN(A2) to confirm string length. If a date string like "2026-05-15" returns a length greater than 10, hidden non-printing characters or spaces exist. Learn how to clean these up in #N/A because of Hidden Non-Printing Characters (CLEAN function fix).
3. Audit Chronological Sequence: Check if any start dates occur after end dates. While NETWORKDAYS returns negative numbers rather than #VALUE! for reversed dates, comparing this behavior against functions like DATEDIF is critical. See #NUM! in DATEDIF: When Start Date is after End Date.

Complexity & Repair Range

• Minor (Local Format Fix): 2 minutes. Applying Text to Columns or wrapping arguments in DATEVALUE().
• Moderate (Holiday Range & Formula Cleanup): 15–30 minutes. Restructuring formula references to exclude text headers and parsing non-standard text separators with SUBSTITUTE().
• Major (Cross-Regional Ingestion Overhaul): 1–2 hours. Standardizing regional date parsing across multi-user templates or implementing Power Query date transformation steps during data import.

Final Calculation

The #VALUE! error in NETWORKDAYS is an explicit signal that Excel cannot translate one or more date inputs into a numeric serial number. To keep project trackers and work-day calculations operating reliably, avoid passing raw text strings into date arguments, exclude text headers from holiday ranges, and standardize raw data imports to native Excel date serials using Text to Columns or Power Query.