XL5: Hang Using WORKDAY Function with Large Number of Days

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

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