If I use XL2007 to open an XL2003 file which uses Networkdays() it appears to
work OK. BUT, when I save the file (as .xls) it corrupts the formulae. The
worst part is that I get no error message on saving, but the next time I open
the file with XL2003 or XL2007, I receive the message: "File Error: Data May
Have Been Lost". If I look at the Networkdays() cells, the numbers (values)
are still there but where there used to be a Networkdays() function, I now
see "=#N/A"!

This is where it gets weirder. If, when I first open the file in XL2007, I
then save it as .xlsx or .xlsm I can re-open it with XL2007 and the
Networkdays() formulae are still working - Great! Not just that, but if I
save it, from XL2007, as .xls (XL2003 format), I am able to reopen it in
XL2003 or XL2007 and I don't get the aberrant behaviour any more. At least I
haven't recently (fingers-crossed!).

This has got to be a BUG, but I see no mention of it in the KnowledgeBase
and nobody in the Community seems to have a solution. Any new leads?

Sean

I've seen a few posts describing this behavior. I don't have a solution for
it but here's an alternative to NETWORKDAYS:

A2 = start date
B2 = end date
C2:C10 = list of holiday dates

If you don't need to exclude holidays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6))

If you do need to exclude holidays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A2&":"&B2)),C2:C10,0))))

--
Biff
Microsoft Excel MVP


"Sean" wrote in message
...
If I use XL2007 to open an XL2003 file which uses Networkdays() it appears
to
work OK. BUT, when I save the file (as .xls) it corrupts the formulae.
The
worst part is that I get no error message on saving, but the next time I
open
the file with XL2003 or XL2007, I receive the message: "File Error: Data
May
Have Been Lost". If I look at the Networkdays() cells, the numbers
(values)
are still there but where there used to be a Networkdays() function, I now
see "=#N/A"!

This is where it gets weirder. If, when I first open the file in XL2007, I
then save it as .xlsx or .xlsm I can re-open it with XL2007 and the
Networkdays() formulae are still working - Great! Not just that, but if I
save it, from XL2007, as .xls (XL2003 format), I am able to reopen it in
XL2003 or XL2007 and I don't get the aberrant behaviour any more. At least
I
haven't recently (fingers-crossed!).

This has got to be a BUG, but I see no mention of it in the KnowledgeBase
and nobody in the Community seems to have a solution. Any new leads?

Sean

Thanks, I'll have to remember that if the proper solution comes up (by which
I mean MS fixing it - any chance?). Problem is that I have so many files
already which use Networkdays().
Thanks,
Sean

"T. Valko" wrote:

I've seen a few posts describing this behavior. I don't have a solution for
it but here's an alternative to NETWORKDAYS:

A2 = start date
B2 = end date
C2:C10 = list of holiday dates

If you don't need to exclude holidays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6))

If you do need to exclude holidays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A2&":"&B2)),C2:C10,0))))

--
Biff
Microsoft Excel MVP


"Sean" wrote in message
...
If I use XL2007 to open an XL2003 file which uses Networkdays() it appears
to
work OK. BUT, when I save the file (as .xls) it corrupts the formulae.
The
worst part is that I get no error message on saving, but the next time I
open
the file with XL2003 or XL2007, I receive the message: "File Error: Data
May
Have Been Lost". If I look at the Networkdays() cells, the numbers
(values)
are still there but where there used to be a Networkdays() function, I now
see "=#N/A"!

This is where it gets weirder. If, when I first open the file in XL2007, I
then save it as .xlsx or .xlsm I can re-open it with XL2007 and the
Networkdays() formulae are still working - Great! Not just that, but if I
save it, from XL2007, as .xls (XL2003 format), I am able to reopen it in
XL2003 or XL2007 and I don't get the aberrant behaviour any more. At least
I
haven't recently (fingers-crossed!).

This has got to be a BUG, but I see no mention of it in the KnowledgeBase
and nobody in the Community seems to have a solution. Any new leads?

Sean

There was a post similar to yours a few days ago but I couldn't reproduce
the behavior on my machine running both Excel 2002 and 2007. ATP loaded in
Excel 2002.

--
Biff
Microsoft Excel MVP


"Sean" wrote in message
...
Thanks, I'll have to remember that if the proper solution comes up (by
which
I mean MS fixing it - any chance?). Problem is that I have so many files
already which use Networkdays().
Thanks,
Sean

"T. Valko" wrote:

I've seen a few posts describing this behavior. I don't have a solution
for
it but here's an alternative to NETWORKDAYS:

A2 = start date
B2 = end date
C2:C10 = list of holiday dates

If you don't need to exclude holidays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6))

If you do need to exclude holidays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A2&":"&B2)),C2:C10,0))))

--
Biff
Microsoft Excel MVP


"Sean" wrote in message
...
If I use XL2007 to open an XL2003 file which uses Networkdays() it
appears
to
work OK. BUT, when I save the file (as .xls) it corrupts the formulae.
The
worst part is that I get no error message on saving, but the next time
I
open
the file with XL2003 or XL2007, I receive the message: "File Error:
Data
May
Have Been Lost". If I look at the Networkdays() cells, the numbers
(values)
are still there but where there used to be a Networkdays() function, I
now
see "=#N/A"!

This is where it gets weirder. If, when I first open the file in
XL2007, I
then save it as .xlsx or .xlsm I can re-open it with XL2007 and the
Networkdays() formulae are still working - Great! Not just that, but if
I
save it, from XL2007, as .xls (XL2003 format), I am able to reopen it
in
XL2003 or XL2007 and I don't get the aberrant behaviour any more. At
least
I
haven't recently (fingers-crossed!).

This has got to be a BUG, but I see no mention of it in the
KnowledgeBase
and nobody in the Community seems to have a solution. Any new leads?

Sean

I have been experiencing this problem with the EDATE function since upgrading
to 2007. The workbook was originally created in XL2003, and uses the EDATE
function to increase the date by 1 mo across a series of columns. After
opening the file in compatibility mode in 2007, making a minor change, saving
and then reopening the file it will corrupt after 3-5 tries. I have applied
SP1, but it did not fix the problem.
I could provide a copy of a file that works now but which I believe will
corrupt after 3-5 change and saves, if someone would like to take a look at
it. I'm unsure of how to best pursue a solution; this is a significant issue
for us as this workbook is in use across the company, and reworking the
formula in all of the workbooks is not an option.



"T. Valko" wrote:

There was a post similar to yours a few days ago but I couldn't reproduce
the behavior on my machine running both Excel 2002 and 2007. ATP loaded in
Excel 2002.

--
Biff
Microsoft Excel MVP


"Sean" wrote in message
...
Thanks, I'll have to remember that if the proper solution comes up (by
which
I mean MS fixing it - any chance?). Problem is that I have so many files
already which use Networkdays().
Thanks,
Sean

"T. Valko" wrote:

I've seen a few posts describing this behavior. I don't have a solution
for
it but here's an alternative to NETWORKDAYS:

A2 = start date
B2 = end date
C2:C10 = list of holiday dates

If you don't need to exclude holidays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6))

If you do need to exclude holidays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A2&":"&B2)),C2:C10,0))))

--
Biff
Microsoft Excel MVP


"Sean" wrote in message
...
If I use XL2007 to open an XL2003 file which uses Networkdays() it
appears
to
work OK. BUT, when I save the file (as .xls) it corrupts the formulae.
The
worst part is that I get no error message on saving, but the next time
I
open
the file with XL2003 or XL2007, I receive the message: "File Error:
Data
May
Have Been Lost". If I look at the Networkdays() cells, the numbers
(values)
are still there but where there used to be a Networkdays() function, I
now
see "=#N/A"!

This is where it gets weirder. If, when I first open the file in
XL2007, I
then save it as .xlsx or .xlsm I can re-open it with XL2007 and the
Networkdays() formulae are still working - Great! Not just that, but if
I
save it, from XL2007, as .xls (XL2003 format), I am able to reopen it
in
XL2003 or XL2007 and I don't get the aberrant behaviour any more. At
least
I
haven't recently (fingers-crossed!).

This has got to be a BUG, but I see no mention of it in the
KnowledgeBase
and nobody in the Community seems to have a solution. Any new leads?

Sean

Sure. Upload it to www.savefile.com and provide the link. I'll look at it.

Tyro

"amcnelis" wrote in message
...
I have been experiencing this problem with the EDATE function since
upgrading
to 2007. The workbook was originally created in XL2003, and uses the EDATE
function to increase the date by 1 mo across a series of columns. After
opening the file in compatibility mode in 2007, making a minor change,
saving
and then reopening the file it will corrupt after 3-5 tries. I have
applied
SP1, but it did not fix the problem.
I could provide a copy of a file that works now but which I believe will
corrupt after 3-5 change and saves, if someone would like to take a look
at
it. I'm unsure of how to best pursue a solution; this is a significant
issue
for us as this workbook is in use across the company, and reworking the
formula in all of the workbooks is not an option.



"T. Valko" wrote:

There was a post similar to yours a few days ago but I couldn't reproduce
the behavior on my machine running both Excel 2002 and 2007. ATP loaded
in
Excel 2002.

--
Biff
Microsoft Excel MVP


"Sean" wrote in message
...
Thanks, I'll have to remember that if the proper solution comes up (by
which
I mean MS fixing it - any chance?). Problem is that I have so many
files
already which use Networkdays().
Thanks,
Sean

"T. Valko" wrote:

I've seen a few posts describing this behavior. I don't have a
solution
for
it but here's an alternative to NETWORKDAYS:

A2 = start date
B2 = end date
C2:C10 = list of holiday dates

If you don't need to exclude holidays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6))

If you do need to exclude holidays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A2&":"&B2)),C2:C10,0))))

--
Biff
Microsoft Excel MVP


"Sean" wrote in message
...
If I use XL2007 to open an XL2003 file which uses Networkdays() it
appears
to
work OK. BUT, when I save the file (as .xls) it corrupts the
formulae.
The
worst part is that I get no error message on saving, but the next
time
I
open
the file with XL2003 or XL2007, I receive the message: "File Error:
Data
May
Have Been Lost". If I look at the Networkdays() cells, the numbers
(values)
are still there but where there used to be a Networkdays() function,
I
now
see "=#N/A"!

This is where it gets weirder. If, when I first open the file in
XL2007, I
then save it as .xlsx or .xlsm I can re-open it with XL2007 and the
Networkdays() formulae are still working - Great! Not just that, but
if
I
save it, from XL2007, as .xls (XL2003 format), I am able to reopen
it
in
XL2003 or XL2007 and I don't get the aberrant behaviour any more. At
least
I
haven't recently (fingers-crossed!).

This has got to be a BUG, but I see no mention of it in the
KnowledgeBase
and nobody in the Community seems to have a solution. Any new leads?

Sean

I'm experiencing the same problem.

I'm using an xls file between Excel 2007 and Excel 2002 (SP3). On two
occasions (in the space of two weeks) it has randomly had this issue
(both times on excel 2007). But, it goes beyond the Networkdays()
formula and also affects and EOMONTH formulas in the same way, see:
http://www.themssforum.com/Worksheet/EOMONTH-Formula/

Saving the affected file as xlsm and reopening does not fix the
problem.

Unfortunately I can't duplicate the problem. It is intermittant (the
worst type of problem!).

Sean;2608806 Wrote:
If I use XL2007 to open an XL2003 file which uses Networkdays() it
appears to
work OK. BUT, when I save the file (as .xls) it corrupts the formulae.
The
worst part is that I get no error message on saving, but the next time
I open
the file with XL2003 or XL2007, I receive the message: "File Error:
Data May
Have Been Lost". If I look at the Networkdays() cells, the numbers
(values)
are still there but where there used to be a Networkdays() function, I
now
see "=#N/A"!

This is where it gets weirder. If, when I first open the file in
XL2007, I
then save it as .xlsx or .xlsm I can re-open it with XL2007 and the
Networkdays() formulae are still working - Great! Not just that, but if
I
save it, from XL2007, as .xls (XL2003 format), I am able to reopen it
in
XL2003 or XL2007 and I don't get the aberrant behaviour any more. At
least I
haven't recently (fingers-crossed!).

This has got to be a BUG, but I see no mention of it in the
KnowledgeBase
and nobody in the Community seems to have a solution. Any new leads?

Sean




--
MrCurly