Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different results: Networkdays versus date subtraction
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different results: Networkdays versus date subtraction
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 "OxonLad" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different results: Networkdays versus date subtraction
On Wed, 18 Jun 2008 05:28:01 -0700, OxonLad
wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different results: Networkdays versus date subtraction
Thanks Mike,
So, networkdays is not the number of working days between 2 dates "Mike H" wrote: 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 "OxonLad" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different results: Networkdays versus date subtraction
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? -- David Biddulph "OxonLad" wrote in message ... Thanks Mike, So, networkdays is not the number of working days between 2 dates "Mike H" wrote: 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 "OxonLad" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different results: Networkdays versus date subtraction
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 "David Biddulph" wrote: 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? -- David Biddulph "OxonLad" wrote in message ... Thanks Mike, So, networkdays is not the number of working days between 2 dates "Mike H" wrote: 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 "OxonLad" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Different results: Networkdays versus date subtraction
Thanks to all, I have the clarity I need
"Andrew Ball" wrote: 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 "David Biddulph" wrote: 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? -- David Biddulph "OxonLad" wrote in message ... Thanks Mike, So, networkdays is not the number of working days between 2 dates "Mike H" wrote: 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 "OxonLad" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering dates results in subtraction of numbers | Excel Worksheet Functions | |||
date and time in 2 cells to create subtraction in 3rd | New Users to Excel | |||
Date subtraction -How to not show negative when 2nd date not entered | New Users to Excel | |||
NETWORKDAYS calculation returns inconsistent results | Excel Worksheet Functions | |||
Grouping the results of Networkdays | Excel Discussion (Misc queries) |