From Yahoo Y2K News dated today <:)=

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.

-- Sysman (y2kboard@yahoo.com), August 23, 1999

Answers

Sysman- You beat me to it. Just got my 8/23 PC Geek this AM

-- lurker (lurker@eyespy.net), August 23, 1999.

Sysman,

I found that thing with the Date() function about a year ago when working with some customers' spreadsheets. When I showed it to several people within their company that should care, they blew it off. That's typical of the responses we get from most small business owners. Another typical one was a network we went in on recently that was running on 386's with a version of Novell that came over with Noah. The owner said he knew he was okay with Y2K because their accounting vendor said their software was okay. Didn't seem to matter to him that his hardware and operating system needed some work as well.

One last good one for the road. A doctor's office with 13 workstations on a Novell network had received several letters from the vendor of their practice management system stating they were Y2K ready. The office manager wasn't sure, so she started testing. First appointment she scheduled out into 2000 locked the whole works up. It got to be even more of a mess when they then got a letter from the software company notifying them that they had decided to discontinue the product. They assured them that it had nothing to do with Y2K, just that they were going in more profitable directions.

-- Greg Sugg (gregsugg@bbnp.com), August 23, 1999.