Why is Excel's Networkdays returning incorrect values?-CodePudding

I am trying to use a Networkdays formula in excel, but the values it is returning are incorrect, both when I use a cell value and when I input the values directly. My formula is

=NETWORKDAYS(1/14/2023,H13)

Which returns 32091, and the cell is formatted as General and H13 is a Date cell with '1/2/2023' Using the formula directly

=NETWORKDAYS(1/14/2023,1/2/2023)

I get a result of 0.

I am using Office 365.

CodePudding user response：

This is due to the format.

From NETWORKDAYS() help:

Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2012,5,23) for the 23rd day of May, 2012. Problems can occur if dates are entered as text.

If you use

=NETWORKDAYS(DATE(2023,1,15),H13)

and, optionally, format H13 using the DATE() function:

=DATE(2023,2,1)

You will get 13.

To ensure you don't have any issues with date formats (e.g. 01/02/2023 being ambiguous between the UK/US) consider using DATEVALUE().

CodePudding user response：

Besides @SuperUser's answer, you can still also directly use strings:

=NETWORKDAYS("1/14/2023",H13)
=NETWORKDAYS("1/14/2023","1/2/2023")

The problem of course is if the Excel file is used on another environment with different date formats, as the strings will not be automatically converted. If the content of H13 happens to be a string, you will still have this issue even if you use =NETWORKDAYS(DATE(2023,1,14),H13), so you will need to have this in mind. Either it's a non-issue in your case, or this will need to be handled differently.

Without the quotes, what you are doing here are divisions. You are telling Excel to do 1/14 and then /2023. Put that in a formula cell alone (=1/24/2023) and you will see.