networkdays function returning #VALUE! error

Question

Hello,

I have the following data in excel:

Assuming that the data starts in cell A1, I am inputting =networkdays(A1,eomonth(A1,0),A12:A19). This is returning a #VALUE! error. I have tried to designate all the cells as DATE format and I am still getting the same error. I have even tried to use a TEXT function and format the date in a MMM-DD-YYY format but to no avail.

Is this a quirk in excel worksheets? Hope you can help. Thank you.

Regards,

Sam.

Answer 1

Hi Sam. I am an Excel user like you.

I duplicated your issue by typing the dates in the way you show them. When those dates are typed in like that Excel does not recognize them as dates. Even if you change the cell formatting that will not change the fact that Excel views them as numbers stored as text, and as long as Excel views them that way the formulas cannot work with them.

You can check to see if Excel recognizes them as numbers/dates by changing the format of the cell to Number, or Currency, or Accounting and back to Date. If they do not change, then they are stored as text and the formulas will not work with them.

The dates must be entered in a format that Excel recognizes as a date like these formats.

3/7/2024

3/7/24

3-7-2024

3-7-24

Mar 7, 2024

Mar 7, 24

March 7, 2024

March 7, 24

The issue with the entries in your sample is that there is no comma, "/", or "-" to identify them as dates to Excel. Enter them in the easiest form because it doesn't matter how you enter them as long as Excel recognizes them you can format them however you want them to look.

After entering the dates, you can re-format them to look like the dates you have above if you want, but the initial entry must be in a recognized date format. To format them that way, after entering them to be recognized as dates, select all of the cells to be formatted and open the Format Cells dialogue>Number>Custom and enter this format in the Type line.

mmm dd yyyy

Other date formats are also available under Date in the Category list on the left of the dialogue box.

Once you have entered the dates as numbers your formulas will work.

Answer 2

Here is the link to my basic template:

https://www.dropbox.com/scl/fi/0moqf1cobhq3lrs5vvhiy/Networkdays-Template.xlsx?rlkey=jnrfm5vm54d0t3ammgrvedt6y&dl=0

Hope you can help.

Answer 3

Could you share us a test workbook to check formula?

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

=NETWORKDAYS(A1,EOMONTH(A1,0),$A$12:$A$19)