Can't get NETWORKDAYS to calc elapsed banking days in a month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

flecky - 22 May 2008 15:20 GMT

I want to calculate the number of banking days in a month, and so NETWORKDAYS
and its "holiday" argument is the relevant formula in Excel 2007.
For each date, I can use EOMONTH(date,-1) to get the start of the month date.

Try 1 Jan 2008 without any "Holiday" argument - the result is 2 which seems
to contradict the definition of the formula ".. between 2 dates .." as this
result seems to be including the Start and the End dates themselves.

Next, I tried Mon 5 May 2008 with a Holiday range which has 1 May 2008 in it.
For this, I put the EOMONTH formula in a separate cell, then did a simple
'take one from the other' which gave 5 days - which is logical, being the 1st
to the 5th of May.

But a NETWORKDAYS without a Holiday argument gave 4 - huh?
It should have excluded 2 weekend days.

Then, a NETWORKDAYS with the Holiday argument of 1 May gave 3 - but I would
expect 2, being Friday 2 May and Monday 5 May.

Help! Panic!

Reply to this Message

Bob Phillips - 22 May 2008 15:44 GMT

Use =EOMONTH(A1,-1)+1

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I want to calculate the number of banking days in a month, and so
>NETWORKDAYS
[quoted text clipped - 23 lines]
>
> Help! Panic!

Reply to this Message

Bernie Deitrick - 22 May 2008 15:44 GMT

flecky,

Don't Panic!

You just need to use EOMONTH(Date,-1) +1 to get the first of the month, since EOMONTH returns, not
surprisingly, the LAST day of the month, not the first day. Format the cell for a date, and you will
see that the date is actually April 30, not May 1.

HTH,
Bernie
MS Excel MVP

>I want to calculate the number of banking days in a month, and so NETWORKDAYS
> and its "holiday" argument is the relevant formula in Excel 2007.
[quoted text clipped - 16 lines]
>
> Help! Panic!

Reply to this Message