Different results: Networkdays versus date subtraction

[OxonLad]

23/06/2008 00:00:00
25/06/2008 00:00:00
2.00 3.00
=a2-a1 =NETWORKDAYS(A1,A2)

2 dates in cells A1 and A2, a subtraction results in 2 but the networkdays
function returns 3, why might this be?

Thank You

 

[Mike H]

Hi,

Excel dates are numbers. Format A1 as general to see the number.
The 2 functions work in different ways:-

39622 and 39624 are the numbers that represent your dates so
A2-A1 when is simply (39624- 39622) =2

But when you apply the Networkdays formula both these days and the
intervening day are workdays so it evaluates as 3

Mike

 

[Ron Rosenfeld]

23/06/2008 00:00:00
25/06/2008 00:00:00
2.00 3.00
=a2-a1 =NETWORKDAYS(A1,A2)

2 dates in cells A1 and A2, a subtraction results in 2 but the networkdays
function returns 3, why might this be?

Thank You

NETWORKDAYS includes the first day in the count. Simple subtraction does not.
--ron

 

[OxonLad]

Thanks Mike,
So, networkdays is not the number of working days between 2 dates

 

[David Biddulph]

NETWORKDAYS counts inclusively. Monday 16th to Wednesday 18th counts as 3
days for NETWORKDAYS, but the difference18-16 gives 2.
Are you counting fence posts or are you counting the spaces between them?

 

[Andrew Ball]

As the others said, NETWORKDAYS counts every day, so if you put the same date
in both cells, you get the answer "1", you will also find a problem, the
first week you encouter a national, bank or business holiday that you want
Excel to ignore.

Try this, it works with UK date format as shown below, if you want US format
you might have to experiment, but once you've got it working, its easier to
amend..

1) In cell A1 enter the first date in dd/mm/yyyy format
2) In cell B1 enter the second date also in dd/mm/yyyy format
3) In cell C1 enter "=NETWORKDAYS(A1,B1,Holidays!$B$2:$B$15)-1" without the
quotes. Either cut & paste, or copy it VERY carefully, one tiny mistake and
you're scuppered!
4) Rename the next worksheet "Holiday" without the quotes or the exclamation
mark you can see in the formula
5) List all the known business holidays (Easter, Xmas, etc, in cells B2 to
B15 on the holiday worksheet, if you have more or less business holidays than
we do, amend the B15 figure in step 3 accordingly.
6) The "-1" in the formula in step 3 stops Excel counting both the first and
the second dates as part of the answer, eg 17/06/2008 to 18/06/2008 will give
the answer "2" as it counts both the 17th and the 18th, but I needed it to
calculate the answer as one business day later, hence the need for the "-1".
7) If you need the numer of days actually between the dates, i e Monday to
Friday give three days between, use "-2" at the end of the formula in step 3

Hope that helps, took me ages the first time, the Excel help function wasn't
the best on the subject

Andrew

 

[OxonLad]

Thanks to all, I have the clarity I need

As the others said, NETWORKDAYS counts every day, so if you put the same date
in both cells, you get the answer "1", you will also find a problem, the
first week you encouter a national, bank or business holiday that you want
Excel to ignore.

Try this, it works with UK date format as shown below, if you want US format
you might have to experiment, but once you've got it working, its easier to
amend..

1) In cell A1 enter the first date in dd/mm/yyyy format
2) In cell B1 enter the second date also in dd/mm/yyyy format
3) In cell C1 enter "=NETWORKDAYS(A1,B1,Holidays!$B$2:$B$15)-1" without the
quotes. Either cut & paste, or copy it VERY carefully, one tiny mistake and
you're scuppered!
4) Rename the next worksheet "Holiday" without the quotes or the exclamation
mark you can see in the formula
5) List all the known business holidays (Easter, Xmas, etc, in cells B2 to
B15 on the holiday worksheet, if you have more or less business holidays than
we do, amend the B15 figure in step 3 accordingly.
6) The "-1" in the formula in step 3 stops Excel counting both the first and
the second dates as part of the answer, eg 17/06/2008 to 18/06/2008 will give
the answer "2" as it counts both the 17th and the 18th, but I needed it to
calculate the answer as one business day later, hence the need for the "-1".
7) If you need the numer of days actually between the dates, i e Monday to
Friday give three days between, use "-2" at the end of the formula in step 3

Hope that helps, took me ages the first time, the Excel help function wasn't
the best on the subject

Andrew