networkdays - Excel 2007 drops some days

[Bill Roberts]

I write macros for stock analysis using Excel 97. The macro uses the
expression “=NETWORKDAYS(37545,NOW(),0)â€, returning a value of 1654. The
beginning date is 7/22/2002, and the end date is 2/13/2009. Runs just fine.
When I run the macro on a friend’s computer with Excel 2007, it calculates
the same value for networkdays, but it always DROPS 12/9/2009 (Tuesday) and
12/10/2009 (Wednesday). There is definitely stock data for those dates.
Consequently, the beginning date on his computer is 7/18/2002 (2 days
earlier), and the end date is 2/13/2009.
Why does Excel 2007 do that??? TIA.

 

[JE McGimpsey]

I'm finding what you're saying confusing...

37545 corresponds to 16 October 2002, not 22 July 2002.

=NETWORKDAYS(DATE(2002,7,22),TODAY()) returns 1716, not 1654

12/9/2009 is a Wednesday, and 12/10/2009 is a Thursday.

Not sure why you're using 0 as a holiday (corresponding to 12/31/1899)

If the NETWORDAYS function returns the same value on both machines,
what do you mean by "it always DROPS 12/9/2009"...

If you're using a serial number, how are you determining that the start
date is changing??


BTW: Not sure why you're using NOW() which includes the time, rather
than TODAY(), which doesn't.

 

[Bill Roberts]

Sorry for the confusion;
1. I picked 37545 because it seems to drive the start date to about 7/22/02.
I don't care what the number is, I want the data to start about 7/22/2002.
37545 seems to work OK, and I assume it has to do with workdays between
7/22/2002 and 10/16/2002.
2. BIG APOLOGIES. I meant 2008, NOT 2009. I think the weekdays (Tuesday
and Wednesday) are correct.
3. I thought "0" in networkdays related to "ignore all holidays". It seems
to work OK in the macro.
4. You're right, I should use today(), not now(). I changed the macro, and
it seems to work OK with either function.
5. The networkdays function returns the same number (1654) on both versions
of Excel, but in Excel 2007 it skips 12/9/2008 and 12/10/2008. It jumps from
12/8/2008 to 12/11/2008. Since both versions return 1654, it follows that
Excel2007 starts at 7/18/2002, whereas Excel97 starts at 7/22/2002. Both end
at 2/13/2009, but Excel2007 does not return any data for 12/9/2008 and
12/10/2008. That is my question. Just those 2 days. None others. Any
reason why?? Thanks for your help.

 

[JE McGimpsey]

Perhaps someone else will jump in, because I still don't get it...

1) Not sure what you mean by "drive the start date". XL stores dates as
integer offsets from a base date, with (in the 1900 date system), 0
corresponding to 12/31/1899. 37,545 days after that date (ignoring the
error of 2/29/1900) is 16 October 2002.

It's not a matter of "driving" anything: If you want to start on
7/22/2002, then you MUST use 37459 (which is the value returned by
DATE(2002,7,22)).

2) I should have figured.

3) No, look at Help - any third argument is taken as a holiday range
reference or list. Of course, since your dates were all > 0, the 0 is
ignored.

4) You won't see any difference in the result - NETWORKDAYS just has to
truncate the time portion of NOW(), leaving the value returned by
TODAY().

5) Still don't have the faintest idea why you've evaluated that XL07 is
"skipping" anything.

You've got a *fixed* start date - 16 October 2002. It can't change.

There have been 1654 weekdays since that date. There have been 1716
weekdays since 22 July 2002.

You've said that "Excel2007 does not return any data for 12/9/2008 and
12/10/2008".

But your formula doesn't directly reference those two dates. So how do
you know XL07 is skipping them? What data are you expecting to be
returned for those two dates? What functions are you using to return
that data?

 

[Ron Rosenfeld]

I write macros for stock analysis using Excel 97. The macro uses the
expression “=NETWORKDAYS(37545,NOW(),0)”, returning a value of 1654. The
beginning date is 7/22/2002, and the end date is 2/13/2009. Runs just fine.
When I run the macro on a friend’s computer with Excel 2007, it calculates
the same value for networkdays, but it always DROPS 12/9/2009 (Tuesday) and
12/10/2009 (Wednesday). There is definitely stock data for those dates.
Consequently, the beginning date on his computer is 7/18/2002 (2 days
earlier), and the end date is 2/13/2009.
Why does Excel 2007 do that??? TIA.

37545 is how Excel stores 16 Oct 2002 (or 17 Oct 2006 in the 1904 date system).
How did you determine a beginning date of 7/22/2002 or 7/18/2002?

9 Dec 2009 and 10 Dec 2009 definitely do NOT have any stock data unless you
have access to a time machine.

Why do have a '0' for the Holidays argument of the Networkdays function? It
means to consider 31 Dec 1899 as a holiday, but that was a Sunday, I believe,
and is also not in your range of dates.

How did you determine that NetWorkdays was skipping those two days? What two
days is it substituting? (It would have to substitute two other days in order
for the answer to be the same).

If you are using the same value in the Networkdays function for the start date
(37545), it cannot differ by two days between Excel 2003 and Excel 2007.

Most likely you are either misinterpreting something, or not clearly stating
what you mean.
--ron

 

[Bill Roberts]

Ron, there may be a bit of an overlap in your reply, so here is my current
summary (hopefully more clear)
1. I want the macro to load data from (roughly) 7/22/2002 to the current
date, and I want 7/22/2002 to remain the start date for the data as time goes
by. I determined a beginning date of 7/22/2002 from the stock data I want to
capture. I don’t care what the actual datevalue is for 7/22/2002.
2. I found that 37545 in the networkdays function gives me a start date
close to 7/22/2002. I understand that it is not the “correct†datevalue, but
if you use the correct datevalue (7/22/2002 has a datevalue of 37459) the
rows will not begin at 7/22/2002, but at 4/22/2002. I don’t know why, but I
know it works.
3. Both you and JE correctly pointed out that I should use today(), instead
of now(), and I don’t need the “0†in the networkdays function. However, the
code runs OK with those mistakes (I changed it).
4. My missing Excel 2007 dates (as in the previous post) should be 2008, not
2009.
5. I determined Excel 2007 was skipping those two dates because I took my
macro from my computer to another computer that has Excel 2007. To make a
long story short, on his computer it has the same number of rows (1654),
starts 2 days earlier, ends on the same day as mine, and has no data for
12/9/2008 and 12/10/2008. Excel 2007 is “substituting†2 days at the
beginning for 12/9/2008 and 12/10/2008. For some reason Excel 2007 does not
return any data for those days, and the error is consistent.
6. My question is: can anyone familiar with the networkdays function
difference between Excel97 and Excel 2007 suggest why this is happening?

 

[JE McGimpsey]

Bill, you're practicing "voodoo XL" here:

- what you're saying doesn't make any sense,
- you don't know why what "works" for you is working, and
- you're not answering the last and most important question:

You formula, *as you've presented it*, has NOTHING to do with those
dates in December 2008. Obviously you've got some sort of list going,
but you're not telling us how it's relevant to your problem.

NETWORKDAYS() CANNOT, CANNOT, CANNOT "gives (you) a start date close to
7/22/2002". Your sentence makes NO sense at all.

NETWORKDAYS() takes dates as arguments - it gives a result in elapsed
days, not dates.

I'm SURE you know more about what you're doing than you're
communicating, but it's hard to help unless you give more detail.

Try to read through your post with the eye of someone who can't see your
data or your formulae.

NOBODY will be able to figure out why you think XL is "skipping" dates
unless you give details that explain EXACTLY how you're determining that
those dates are being "skipped".

 

[Ron Rosenfeld]

Ron, there may be a bit of an overlap in your reply, so here is my current
summary (hopefully more clear)
1. I want the macro to load data from (roughly) 7/22/2002 to the current
date, and I want 7/22/2002 to remain the start date for the data as time goes
by. I determined a beginning date of 7/22/2002 from the stock data I want to
capture. I don’t care what the actual datevalue is for 7/22/2002.
2. I found that 37545 in the networkdays function gives me a start date
close to 7/22/2002. I understand that it is not the “correct” datevalue, but
if you use the correct datevalue (7/22/2002 has a datevalue of 37459) the
rows will not begin at 7/22/2002, but at 4/22/2002. I don’t know why, but I
know it works.
3. Both you and JE correctly pointed out that I should use today(), instead
of now(), and I don’t need the “0” in the networkdays function. However, the
code runs OK with those mistakes (I changed it).
4. My missing Excel 2007 dates (as in the previous post) should be 2008, not
2009.
5. I determined Excel 2007 was skipping those two dates because I took my
macro from my computer to another computer that has Excel 2007. To make a
long story short, on his computer it has the same number of rows (1654),
starts 2 days earlier, ends on the same day as mine, and has no data for
12/9/2008 and 12/10/2008. Excel 2007 is “substituting” 2 days at the
beginning for 12/9/2008 and 12/10/2008. For some reason Excel 2007 does not
return any data for those days, and the error is consistent.
6. My question is: can anyone familiar with the networkdays function
difference between Excel97 and Excel 2007 suggest why this is happening?

My analysis of this and your other posts is that the NetWorkdays function is
working properly on both machines, and has nothing to do with the skipping
dates problem you are seeing.

Most likely, the problem is someplace else within your macro.

I'm not even sure what use you are making of the NetWorkdays function.
--ron

 

[Bill Roberts]

It is entirely possible skipping the dates is not related to the networkdays
function. All I know is: when I run the macro using Excel 97, I get a column
of dates (from 7/22/2002 to 2/13/2009), and other stock data (open, close,
etc). And I have data (on computer #1) for 12/9/2008 and 12/10/2008.
When I run the same macro on a computer with Excel 2007, those dates do not
show in the column. It skips from 12/8/2008 to 12/11/2008.
My best guess (now) is that the data on computer #2 is corrupt such that the
networkdays function doesn't recognize those as valid dates. I don't think
the problem is in the macro. Thanks for the help. Sorry I didn't explain the
problem adequately.

 

[Ron Rosenfeld]

It is entirely possible skipping the dates is not related to the networkdays
function. All I know is: when I run the macro using Excel 97, I get a column
of dates (from 7/22/2002 to 2/13/2009), and other stock data (open, close,
etc). And I have data (on computer #1) for 12/9/2008 and 12/10/2008.
When I run the same macro on a computer with Excel 2007, those dates do not
show in the column. It skips from 12/8/2008 to 12/11/2008.
My best guess (now) is that the data on computer #2 is corrupt such that the
networkdays function doesn't recognize those as valid dates. I don't think
the problem is in the macro. Thanks for the help. Sorry I didn't explain the
problem adequately.

Not much more to say to help you given the very limited information you've
provided.
--ron