F
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.
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.