NETWORKDAYS incompatible with XL2010?

[skysurfer]

Hi,

I created a document in XL2003 using the function NETWORKDAYS...no problem.

But when I use the same file in XL2010, an error message pops up on file open saying NETWORKDAYS is a user-defined function from an older version of Excel (which it's not) and should be deleted and the already existing function by the same name should be used.

The Analysis ToolPak is turned on in both Excels.

What's going on???

Lawrence

[Whizbang]

Do you have a function in the workbook, or in your personal workbook, named NETWORKDAYS?

Try removing the Analysis ToolPak Add-In. NETWORKDAYS works just fine in 2010 without the Analysis ToolPak.

What happens when you type =NETWORKDAYS into a cell, does it give you a list of matching funcitons? There should be only one. If there are two, then there is something screwy going on.

[skysurfer]

Thanks for the clues to solving the mystery...

When I start typing "=NETWORKDAYS" in XL2010 I get a popup listing 3 options: Networkdays international, networkdays all in caps and networkdays in Sentence case. The last two choices yield the same result. Weird.

So, I unselected the Analysis ToolPak and tried the same test. Same result. Why two options that do the same thing???

Then I tried another Analysis ToolPak function: EOMONTH. Check it out. I got 2 options: EOMONTH and EoMonth. Both do the same thing.

What's going on? Anyway, I rewrote all the NETWORKDAYS formula in 2010 and the error on file open stopped. But why do I have copies of the same functions?

Lawrence

[Whizbang]

This is just a total shot in the dark, but...

When you hit ALT+F11, do you see a PERSONAL.XLS in the navigation pane on the left? Is it just .XLS, or .XLSB, or what?

Maybe your personal workbook is still in the 2003 format. I dunno.

[skysurfer]

That's interesting...

In the VBA menu on the left I do not see PERSONAL.XLS. But I do have 2 items in addition to my VBAProject that I have never seen before:

atpvbaen.xls
funcres

Both are locked with a password.

Lawrence

[Whizbang]

funcres is the problem. That is part of the analysis toolpak. In the VBE (what you are in after you hit ALT+F11), go to Tools -> References and deselect anything with "Analysis ToolPak" in the name.

[Whizbang]

I am sorry. I spoke before I researched. Go to your Developer tab, then Add-Ins and deselect Analysis ToolPak - VBA

[Whizbang]

Ok. Even more research done. Both of thos files are the Analysis ToolPak. But the .xls extension tells me they are the 2003 toolpak, not the 2010.

What other Workbooks do you have open? Are any of them .xls? There must be something that is still trying to reference the 2003 Analysis ToolPak

[skysurfer]

Thanks for all the help...this site always comes though!

I have a solution, but I do not know if it's permanent. I rewrote all the NETWORKDAYS formulas in xl2010 and kept the xls extension. I still have multiple NETWORKDAYS functions, but at least I no longer get errors on file open. The file works smoothly in both xl2003 and xl2010.

Thing is, I wish I knew why there would be a lack "fidelity" (to use Microsoft's word) between the 2 programs.

Thanks again!

Lawrence

[Whizbang]

Lack of fidelity comes from little things like cell fill color, or other formatting. Excel 2010 allows you to use Windows colors, which change with the system settings. Whereas 2003 has fixed color values. For example, go to change the fill color of a cell. You will see "Theme Colors" and "Standard Colors". Theme colors are grabbed from the Windows Theme (changed through display settings in the Control Panel). Standard colors are fixed RGB colors.

Any display feature that is unique to 2007 or 2010 will cause Fidelity issues. It is usually ok to ignore these, because Excel is pretty good at converting these Theme Colors to standard colors when you save as .xls.