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

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!

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

Use =EOMONTH(A1,-1)+1

--
HTH

Bob

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

"flecky" wrote in message
...
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!

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

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


"flecky" wrote in message
...
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!