NETWORKDAYS() saved as =#N/A in XL2007

S

Sean

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
 
T

T. Valko

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))))
 
S

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

T. Valko

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

MrCurly

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!).
 
A

amcnelis

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

Tyro

Upon opening the file I get a message: "File error: data may have been
lost" and I16 displays as Dec-07 but the formula bar shows #N/A. And that
appears to be the case for the other dates in row 16. This suggests that the
file is corrupt. Do you have a copy of the file that is good?

Tyro
 
A

amcnelis

I had to start with a different file, because the one I had been using was
opened too many times. The new file has different data, but the layout, use
of EDATE and the issue are the same. I was able to open my copy of this file
once. I made a change and saved the file. The second time I opened it I
received the file error message and cell I16 on the Monthly Detail tab had
been changed to "=N/A"
Thanks again for taking a look.
 
T

Tyro

If I save your workbook in Excel 2007 without the macros, the workbook is
fine. The EDATE functions remain intact, i.e. no #N/A. It looks like the
macro(s) may be the cause of your problem.

Tyro
 
A

amcnelis

Yes, I can confirm with a test of about 10-15 change and saves:
Save file from 2007 ver with (xlsm) and without (xlsx) macros-no corruption
of EDATE formula
Work with these 2007 ver workbooks in 2003 with the backward converter-no
corruption.
Remove macro from workbook and save as .xls in compatibility mode-no
corruption
Save from 2007 as xls in compatibility mode with macro-file corrupts EDATE
formula in 2-3 saves.

My goal is to be able to work with the files in 2007 ver with compatibility
mode. Shouldn’t I be able to do so with macros that were created and worked
in 2003? For those of you who have also reported on this issue are your
workbooks using macros that were created in 2003?
Thks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Yield() function missing from XL2007 SP1 ATPVBAEN Analysis ToolPak 8
Sub projects added as read only in Master file which are saved on sharepoint 1
Autonumber field in Excel that behaves the same as Access 0
Unable to nest my IF statements properly 2
Copy condtional formatting in XL2007 for Windows 1
How do I prevent a text box from printing in XL2007? 7
xl2007 - Slow Macros Involving Graphics 3
Is NETWORKDAYS Supported in Excel to Web Page? 1

Top