The NETWORKDAYS function in Excel is a useful tool for calculating the number of working days between two dates, excluding weekends and optionally specified holidays. However, if this function isn’t working correctly, the issue is often related to invalid date formats.
Table of contents
YouTube: Excel NETWORKDAYS function not working
Checking if the dates are recognized as valid Excel dates
If NETWORKDAYS returns a #VALUE! error, the first thing to check is whether your start and end dates are recognized as real dates in Excel. To do this, highlight a few of the date cells (see 1 below) and press Ctrl + 1 to open the Format Cells menu. Change the format to Date (2) and alternate between a few of the date types (3), and if the sample doesn’t change (red arrow), it means Excel does not recognize them as dates. In that case, you need to convert them to valid date formats (see how to fix dates in Excel).
Avoid using two-digit years
Another common issue is the use of two-digit years, which can lead to incorrect calculations.
For example, Excel might interpret 01/12/09 as 1st December 1909 when you meant 9th December 2001 or vice versa.
To prevent this, always use four-digit years when entering dates. You can check whether the dates are correct by making sure that you show the dates with a date format using 4 digits for years via the Format Cells menu. Below, the image demonstrates how Excel misinterprets two-digit years, leading to incorrect calculations.
Fixing incorrect cell formatting
Even if your NETWORKDAYS function is calculating correctly, the result might look incorrect if the output cell is formatted as a date instead of a number. If you see a strange date instead of a numerical value, highlight the cell, press Ctrl + 1, and change the format to General or Number. This ensures the function displays the correct count of working days instead of a date.
Handling numbers mistakenly treated as dates
Sometimes, dates might be entered as long numbers rather than proper date formats. For example, if a date is written as 20231231 instead of 31/12/2023, Excel may not interpret it correctly. You can fix this by converting the number into a valid date using Text to Columns or by manually re-entering the date correctly. See more on fixing date issues in Excel
Ensuring holiday dates are properly formatted
If your NETWORKDAYS function includes a list of holidays, ensure that those holiday dates are also seen correctly as valid Excel dates (the date format does not matter, as long as Excel knows it is a date).
If Excel doesn’t recognize a holiday as a date, it won’t exclude it from the calculation. To check this, select the holiday list, press Ctrl + 1, and change the format to Date and try a few date format options. If the formatting does not apply in the sample box, Excel does not recognize them as valid dates, and they need to be corrected.
Tips and troubleshooting
- Always verify that both the start and end dates are valid Excel dates.
- Use four-digit years instead of two-digit years to prevent misinterpretation.
- Ensure that the output cell for NETWORKDAYS is formatted as a number, not a date.
- If dates appear as long numbers (e.g. 20240630) , convert them to proper Excel date.
- Check that holidays used in NETWORKDAYS are recognized as valid dates in Excel.