NETWORKDAY + Excel 2007 + Excel 2003

[Joe]

Hi

I have the following problem...

A workbook created in 2003 Excel version has several cells with formulas.

Some cells has the NETWORKDAY function.

This workbook has to be used by user running Excel 2003 and Excel 2007 in
their computers.

So the workbook has to stay in XLS version (2003 and earlier).

The problem is: when the workbook is opened in Excel 2007, the cells
containing the NETWORKDAY function shows a Error message: VALUE.

The user has to edit the cell to correct the problem.

After saving the workbook in the Excel 2007 (keeping the XLS format) and
openning the file in Excel 2003, the same error occurs.

How can I solve this problem? Where is it?

Thank you in advance for your help.

Joe

 

[T. Valko]

I've seen several reports of this behavior but I don't know the direct cause
or the direct solution.

However, you can use an alternative formula that does exactly the same thing
NETWOKDAYS does and not have to worry about it. One slight drawlback is that
the formula is volatile meaning it recalculates with every calculation.

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

NETWORKDAYS version:

=NETWORKDAYS(A2,B2,C2:C10)

Alternative version:

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

If you don't need the holidays exclusion:

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