When NETWORKDAYS gives an error or a number you don’t expect, the cause is almost always one of the seven below. Each one was reproduced in Google Sheets, with the symptom you will see and the fix. Start with the symptom that matches yours.
| Symptom | Most likely cause |
|---|---|
| #VALUE! | 1 (dates stored as text) or 3 (text in the holiday list) |
| 0 or a tiny number | 2 (dates typed directly in the formula) |
| One day more or less than expected | 4 (both dates are counted) |
| A negative number | 5 (start and end dates swapped) |
| Wrong count for your work week | 6 (different weekend) |
| Error as soon as you press Enter | 7 (wrong argument separator) |
Tested in Google Sheets. On November 9, 2026 we entered the formulas on this page in Google Sheets, each of the seven causes, reproduced on purpose, and compared the results with values calculated independently. How we test
1. The dates are stored as text (#VALUE!)
If a start or end date is text that looks like a date, NETWORKDAYS returns #VALUE!. This is common with dates pasted from other systems or written day-first in a spreadsheet that expects month-first. Check with =ISNUMBER(A2): a real date returns TRUE.
Fix: convert the text to real dates. Our guide to dd/mm/yyyy dates stored as text shows a formula for that.
2. The dates are typed directly in the formula (0 or a tiny number)
=NETWORKDAYS(1/5/2026, 1/30/2026) looks right, but Google Sheets reads 1/5/2026 as “1 divided by 5 divided by 2026”. The result is 0, not the 20 working days you expected.
Fix: use DATE(year, month, day) or cell references:
=NETWORKDAYS(DATE(2026, 1, 5), DATE(2026, 1, 30))
3. The holiday list contains text (#VALUE!)
A single text value in the holiday range is enough to make the whole formula return #VALUE!. The usual culprits are a holiday pasted as text, or a range that includes the header row, such as Holidays!A1:A when A1 contains the word “Date”.
Fix: start the range below the header (Holidays!A2:A) and make sure every holiday is a real date. Empty cells are fine.
4. You expected one day less (both dates are counted)
NETWORKDAYS counts the start date and the end date when they are working days. From Monday to Friday of the same week it returns 5, not 4, and the same date as start and end returns 1.
Fix: if the start date should not count, start from the next day: =NETWORKDAYS(A2+1, B2). See inclusive vs exclusive counting.
5. The start and end dates are swapped (negative number)
If the end date is earlier than the start date, NETWORKDAYS does not return an error: it returns the same number of working days, but negative. January 30 to January 5, 2026 gives -20.
Fix: swap the arguments, or wrap the formula in ABS() if the order can vary and you only need the size of the gap.
6. Your weekend is not Saturday and Sunday
NETWORKDAYS always treats Saturday and Sunday as the weekend. If your team works Sunday to Thursday, or six days a week, the count will be off.
Fix: use NETWORKDAYS.INTL with a weekend code. For a Friday and Saturday weekend:
=NETWORKDAYS.INTL(A2, B2, 7)
Code 11 means only Sunday is a day off. The full list of codes is in NETWORKDAYS with holidays.
7. The formula uses commas in a semicolon locale (error on Enter)
In locales that use a comma as the decimal separator, such as Spain, Germany or Brazil, Google Sheets separates function arguments with semicolons. A formula copied with commas, like the ones on most English-language sites, usually returns an error in those spreadsheets as soon as you press Enter. With simple numbers it can be worse: in a spreadsheet set to Spain, =SUM(1,2) returned 1,2 instead of 3, because the comma was read as a decimal mark.
Found in testing: the problem only goes one way. A formula written with commas returned #ERROR! in a spreadsheet set to Spain, but the same formula written with semicolons still worked in a spreadsheet set to the United States.
Fix: replace each comma between arguments with a semicolon: =NETWORKDAYS(A2; B2). You can check your locale in File → Settings, and our formula checker converts a whole formula for you. More on this in formula parse error: 9 causes.
Not a cause: dates with times
If your cells contain a date and a time, NETWORKDAYS ignores the time and counts whole days. A start of January 5, 2026 at 6:00 PM still counts January 5 as a working day.