Q114856 - XL5: Hang Using WORKDAY Function with Large Number of Days

XL5: Hang Using WORKDAY Function with Large Number of Days

The information in this article applies to:

Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, if you reference the WORKDAY() function in the MS Excel 4.0 Analysis Functions add-in file, ANALYSF.XLA, and you use a large number for the days argument, your computer appears to hang.

CAUSE

A reference on your worksheet to the WORKDAY() function in ANALYSF.XLA does not actually cause your computer hang; however, the function may take a long time to be calculated when you use a large number for the days argument. When you use this function, the number of days is equal to the number of loops that the WORKDAY() function goes through to calculate the result.

WORKAROUND

To avoid this long processing time when you use the WORKDAY() function, do either of the following:

Reference the WORKDAY() function included in the Microsoft Excel version 5.0 Analysis ToolPak file, ANALYSIS.XLL, instead of the function in ANALYSF.XLA with the following steps:
1. In Microsoft Excel, load the Analysis ToolPak add-in by choosing Add-Ins from the Tools menu. From the Add-Ins Available list, select the Analysis ToolPak check box and choose OK
2. On the worksheet, enter the function, as in the following example:
```
         =WORKDAY("1/1/1904",44000) 
```
-or-

To update a link to the Microsoft Excel version 4.0 add-in, ANALYSF.XLA, and to reference this same function in the ANALYSIS.XLL add-in file, use the Update Add-in Links add-in, UPDTLINK.XLA, with the following steps:
1. Open the worksheet that contains the reference to the WORKDAY function in the ANALYSF.XLA add-in.
2. From the Tools menu, choose Add-Ins. From the Add-Ins Available list, select the Update Add-in Links and Analysis ToolPak check boxes. If these options are not available, choose the Browse button, and locate the files UPDTLINK.XLA and ANALYSIS.XLL
3. From the Tools menu, choose MS Excel 4.0 Add-In Links.
4. To update the references in the active workbook only, select Active Document. To update the references in all open workbooks, select All Open Documents. Choose OK.

When you reference the WORKDAY() function in ANALYSIS.XLL, the function is calculated faster than when you reference the WORKDAY() function in ANALYSF.XLA.

For more information about using the Add-In Links add-in, query on the following words in the Microsoft Knowledge Base:

updtlink.xla and update

MORE INFORMATION

The Add-Ins dialog box lists several add-ins that comprise the Analysis Tools. The part of the add-in that contains all of the functionality is the Analysis ToolPak, ANALYSIS.XLL. If you open a Microsoft Excel version 4.0 worksheet that contains an analysis function, such as WORKDAY(), you must load the MS Excel 4.0 Analysis Functions add-in in order to use the function.

If you are no longer going to use this file in Microsoft Excel version 4.0, you should use the Update Add-in Links add-in to update the Analysis function to the Microsoft Excel version 5.0 format. After you do this, and you save the file, you no longer need to load the MS Excel 4.0 Analysis Functions add-in in order for the function to return a result.

REFERENCES

For more information about WORKDAY(), choose the Search button in Help and type:

WORKDAY function

Additional query words: addins atp

Keywords :
Version : WINDOWS:5.0
Platform : WINDOWS
Issue type :
Technology :