CODEWITHSUNDEEP
excelexcel-formulaformula
Rhyfelwr
Rhyfelwr

Reputation: 329

Unreasonably large negative number when using =NETWORKDAYS()

Hi there I am trying to use the =NETWORKDAYS formula in Excel to calculate the number of workdays passed between two dates minus public holidays on a separate sheet. When I enter the correct formula I get an unreasonably large negative number like -29221 workdays between 2017-04-28 to 2018-04-24. I've tried to change the NumberFormat of the cells, ignoring public holidays etc. but I still get these numbers. Am I doing something wrong?

Here's my formula: 

=NETWORKDAYS(2018-4-24,D2,PUBLIC_HOLIDAYS!E44:E61)

Couldn't find anything on the internet that would solve this.

Upvotes: 0

Views: 1175

Answers (2)

SpaghettiCode
SpaghettiCode

Reputation: 327

Static date is being read as text. Enclose it in quotes or use date function. Consider these options:

=NETWORKDAYS("2018-4-24",D2,PUBLIC_HOLIDAYS!E44:E61)

OR

=NETWORKDAYS(DATE(2018,4,24),D2,PUBLIC_HOLIDAYS!E44:E61)

If it still doesn't work, check the cells that you're referencing. Make sure you're referencing dates or its equivalent value and not some random numbers.

Hope this helps..

Upvotes: 0

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

2018-4-24 probably isn't being read as a date value, insert it into a cell and change to general to get it's "value" or use it in Date() like so:

=NETWORKDAYS(DATE(2018,4,24),D2,PUBLIC_HOLIDAYS!E44:E61)

Upvotes: 2

Related Questions