NETWORKDAYS XL2007 problem with calculating Banking Days in a mont

[flecky]

I want to calculate the elapsed number of banking days in the month for a
list of dates.



"I will need to use NETWORKDAYS for each row, and I use Excel 2007 on
Vista."

"The Start_Date will need to be the end of the previous month, so use
=EOMONTH(date,-1)"



Let's take Tues 1st Jan 2008 and assume no holidays at first.


"=NETWORKDAYS(EOMONTH(A3,-1),A3) results in 2 which contradicts the
formula's definition of 'between' the dates."

"If I put the EOMONTH formula in a separate cell, a simple date deduction
gives 1 day, which would be correct."



Now try Mon 5 May 2008 and set up a holiday of Thurs 1st May 2008.


"The NETWORKDAYS formula results in 3 but I would expect 2, i.e. Fri
2nd May and Mon 5 May."



"It looks as though I just need to -1 after the formula, but that causes
a problem with Tues 2 Jan 2007, with 1/1/07 as a holiday."


"The result for this gives 0, when it should be 1, i.e. the Tuesday 2 Jan
07."

"I think the reason is because the Start_date there is a weekend (Sun 31 Dec
06), which is being excluded twice,"

"by the weekend component of the NETWORKDAYS formula, and also the -1 to
get around the ""between"" definition, as noted above."



Anybody else tried this?

Somebody in the banking sector must have solved this at one stage.

 

[Arvi Laanemets]

Hi

NETWORKDAYS() always includes both start and end dates. I.e.
NETWORKDAYS(TODAY()),TODAY()) returns 1 or 0, depending on today being
workday or not.

You may define it as "function, which returns the number of workdays in
given time period". Otherwise you have to decide, which day are you throwing
out - start day or end day!


Arvi Laanemets

 

[flecky]

I think you're correct, Arvi, meaning that the definition needs a bit more
explanation for users.

The solution is to use a formula like this:

=IF(OR(C10=1,C10=7),NETWORKDAYS(B10,A10,A$15:A$21),NETWORKDAYS(B10,A10,A$15:AC$21)-1)

where:
A10 is my date
B10 is =EOMONTH(A10,-1)
C10 =WEEKDAY(B10) in order to sense if the Start_date was a weekend.
(No 'end-of-month's are holiday, and no "my date" will be a holiday or a
weekend, so that avoids extra formulae.)
A15:A21 is the list of my holidays.

 

[Ron Rosenfeld]

I want to calculate the elapsed number of banking days in the month for a
list of dates.



"I will need to use NETWORKDAYS for each row, and I use Excel 2007 on
Vista."

"The Start_Date will need to be the end of the previous month, so use
=EOMONTH(date,-1)"



Let's take Tues 1st Jan 2008 and assume no holidays at first.


"=NETWORKDAYS(EOMONTH(A3,-1),A3) results in 2 which contradicts the
formula's definition of 'between' the dates."

"If I put the EOMONTH formula in a separate cell, a simple date deduction
gives 1 day, which would be correct."



Now try Mon 5 May 2008 and set up a holiday of Thurs 1st May 2008.


"The NETWORKDAYS formula results in 3 but I would expect 2, i.e. Fri
2nd May and Mon 5 May."



"It looks as though I just need to -1 after the formula, but that causes
a problem with Tues 2 Jan 2007, with 1/1/07 as a holiday."


"The result for this gives 0, when it should be 1, i.e. the Tuesday 2 Jan
07."

"I think the reason is because the Start_date there is a weekend (Sun 31 Dec
06), which is being excluded twice,"

"by the weekend component of the NETWORKDAYS formula, and also the -1 to
get around the ""between"" definition, as noted above."



Anybody else tried this?

Somebody in the banking sector must have solved this at one stage.

NETWORKDAYS includes the starting and ending date.

Ordinary subtraction excludes the starting date.

So if you want NETWORKDAYS to work similarly, just add 1 to your starting date:

=NETWORKDAYS(EOMONTH(A3,-1)+1,A3)

--ron