Make a name
for yourself
online!
Monday August 23 10:50 AM EDT
Y2K tools fail to detect Excel bug
Grant Du Bois, ZDNet
Unless users of Microsoft Corp.'s Excel download scanning tools from the company's Web site, their spreadsheets could go haywire when they open their files on Jan. 1.
A Boston-based technology management consulting company has found that an Excel year 2000 error causing drastic math errors went undetected by a handful of Y2K analysis tools.
The core of the problem is that Excel versions through Excel 2000 have a DATE() function that treats all two-digit years as 20th-century dates, regardless of how Excel is configured to handle two-digit dates. As a result, spreadsheets that use the DATE function are partic ularly vulnerable to Y2K problems. (By default, Excel 97's and Excel 2000's other date functions, as well as the software's data entry routines, treat two-digit dates less than 30 as part of the 21st century.)
Compounding the problem is the fact that most Y2K compliance tools don't catch all possible glitches in Excel. Customers checking their Excel spreadsheets for compliance need to be careful to check not only their formulas and macros, but also any user-defined names (or named areas of a worksheet, which can include program logic in addition to worksheet references). Problems in this part of an Excel worksheet are easy to miss because Excel's Edit, Find command excludes them from its searches.
Microsoft officials in Redmond, Wash., said the company has documented the bug and how the date function is designed to handle four-digit numeric parameters on its Y2K Web page and in Excel Help within Excel.
To that end, Office 95, 97 and 2000 products are Y2K-compliant based on the company's three-tier set of compliance definitions, said David Jaffe, Office product manager at Microsoft.
Nevertheless, IT should be aware of the problem, as it's not easily detected, said Allen Falcon, president of Horizon Information Group, in Boston.
"Most of the [analysis] tools on the market today do not look for the date function everywhere it can possibly exist within an Excel workbook," Falcon said. "It's not uncommon for users to create user-defined names that represent formulas and functions. ... The tools don't find the date function within the user-defined names."
Horizon tested the spreadsheet using Viasoft Inc.'s OnMark 2000 Assess versions 3.0 and 4.0, Symantec Corp.'s Norton 2000, 2000Tools Group Inc.'s DateSpy Professional, Greenwich Mean Time-UTA's Check 2000 PC Deluxe, ClickNet Software Corp.'s ClickNet, and Advanced System Technologies Ltd.'s Datefind-db. Each compliance-checking tool failed to find the error, although they flagged several other bugs in the software, according to Falcon.
The only analysis tool that identifies the error, according to Falcon, is IST Development Inc.'s Year 2000 Analysis Suite.
Tina Sarver, a consulting engineer at Greenwich Mean Time's service and support office in Arlington, Va., acknowledged that the latest version of its Check 2000 PC Deluxe didn't discover the Y2K error in the spreadsheet. Subsequently, Sarver filed a "trouble report" with the company's U.K. development team.
"It's an undocumented feature, a bug," she said. "The product is not formatted to find the code."
Officials at Phoenix-based Via soft have taken the issue to product devel opers and will incorporate an update into the company's next release of its OnMark 2000 Assess analysis software.
"[This] is a very specific example of a formula in a spreadsheet," said Dan Rickard, technical support manager for OnMark, a division of Via soft. "Out of 100 million files, only a minute number might have this problem." To put that into perspective, Rickard said a typical Fortune 500 company has about 100 million files.
See Also:
Setting Y2K priorities
Microsoft patches Excel security holes
------------------------------------------------------------------------
Search News Stories Search News Photos
Aug 24 | Aug 23 | Aug 22 | Aug 21 | Aug 20 | Aug 19 | Aug 18 | Aug 17 | Aug 16 | Aug 13
------------------------------------------------------------------------
Questions or Comments
Copyright ) 1996-1999 ZDNet. All rights reserved.
Reproduction in whole or in part in any form or medium without express written permission of ZDNet is prohibited.
ZDNet and the ZDNet logo are trademarks of Ziff-Davis Publishing Company.
-- Anonymous, August 25, 1999