software_excel1

Welcome to Excel with Excel

The following information is provided by Woody's Office Watch (WOW). You can get Woody's newsletter for fast breaking news on Excel, Word, Power Point, Access, and Windows.

File created on Wednesday, December 29, 1999

Many of the consequences for your worksheets of turning a new century are subtle. You may not
encounter date or calculation discrepancies immediately, but will run into them as you open and work with your files.

What do you look for? There are three main situations that may cause you some headaches:

* Any worksheet where the year portion of a date is specified (displayed) with only two digits.

* Any worksheet that uses dates or date functions in some type of calculation, including dates that "trigger" an event to happen.

* Any macros or VBA code that records or returns a date with only two digits for the year.

Specifically, we will take a look at some potential data entry, importing, and exporting hazards with two-digit year entries (and how to avoid these pitfalls). We'll discuss the Date functions that may cause you trouble as we turn the century mark. Have you recorded macros, dabbled in some
VBA programming, or simply adopted a program or macro from someone else? We'll identify a few potential date problems you may run into. We'll explore the three Microsoft Y2K wizards for Excel and show you how to preclude the automatic recalculation that normally occurs when opening
your Excel files (potentially updating date calculations before you've had a chance to check for possible Y2K errors).

HOW EXCEL HANDLES TWO-DIGIT YEAR ENTRIES
Whether you type a date into a cell or import data that contains dates from another application into Excel, you run the risk of introducing inaccuracies if the date entries include only a two-digit year. Mind you, we have all gotten into the habit of using two-digit year entries. It's easy and it's effortless. Just make sure you understand how Excel interprets these dates (and the consequences of using
and exporting these dates).

When you type, import, or copy a date into a cell that uses only two digits for the year (YY) portion of the date, Excel automatically determines the century portion of the year and adds the century to the date. The cell will continue to reflect (display) the entry you typed in, however, look in the Formula Bar to see the changes Excel has made to the date it actually stores (and uses in
calculations). The rules Excel uses to determine the century differ depending on the version of Excel you are using.

In Excel 2000, 97, or Excel 98 for the Macintosh, dates that include the two-digit years 00-29 are interpreted to be in the 21st century and Excel adds 20 in front of the year. Two-digit years from 30-99 are interpreted as the 20th century and Excel adds 19 in front of the year. So that:

You type/import Excel converts to
1/1/00    1/1/2000
1/1/29    1/1/2029
1/1/30    1/1/1930
1/1/99    1/1/1999

If you want to enter a date before January 1, 1930 or after December 31, 2029, you must enter the full four-digit year. Older versions of Excel (95, 5.0, etc) have a different cutoff for the 20th/21st century. Dates that include the two-digit years 00-19 are interpreted to be in the 21st century; dates with two-digit years from 20-99 are interpreted to be in the 20th century. So that:

You type/import Excel converts to
1/1/00    1/1/2000
1/1/19    1/1/2019
1/1/20    1/1/1920
1/1/99    1/1/1999

If you want to enter a date before January 1, 1920 or after December 31, 2019, you must enter the full four-digit year. So what's the problem? The hazard with two-digit year dates is two-fold. First, if you share these dates in a text-based format (like .TXT or .CSV) with other applications, the displayed date (not the stored date) is shared. For example, suppose you enter 1/1/1915 into a cell
that displays a two-digit year (1/1/15). When you export/import this date into another application the date will display as 1/1/15. If the receiving application uses the same date range as Excel, the 1/1/15 date will be interpreted as 1/1/2015., not the original 1/1/1915.

Secondly, if organizations use several different versions of Excel that interpret the century ifferently, then it is possible for discrepancies to appear between worksheets generated with these different versions. For example, suppose you are using Excel 97. You read the date 3/15/25 over the phone to someone, unaware that they are working with Excel 95. On your worksheet, the date is interpreted as March 15, 2025. On their worksheet the date is interpreted as March 15, 1925. You will both see 3/15/25, but the stored dates will be different. Likewise, suppose you pull some date information from your company's proprietary system, which only exports a two-digit year.
When the dates are imported into Excel, the century will be interpreted differently depending on the version of Excel you are using. The results of any calculations based on those dates will differ when the years fall between 20 and 29.

Here's another scenario. Let's say you plan to share some Excel data with other users that either don't have Excel or who are still using Excel 5.0. In order for all the users to read the file, you decide to save the file in the .CSV file format. The file contains two dates - 01/01/2012 and 01/01/2025 - formatted to display only two-digit year dates, which in the .CSV format get saved as 01/01/12 and
01/01/15 respectively. One person opens that file in Excel 5.0 and has their date format set to use dd/mm/yy. The dates are interpreted as 01/01/2012 and 01/01/1925. If another user has their date format set to use yyyy/mm/dd, the dates become 2001/01/12 and 2001/01/25.

The good news is that once the date is entered or imported and the century has been interpreted and stored in the cell, when you share the file with people using other versions of Excel, the cell may only display a two-digit year, but the stored century is carried with it into the other version of Excel.

So what should you do? Simple, format dates to display the full four-digit year to avoid any possible misinterpretation. If you have Excel 97 or 2000, use Microsoft's Y2K Date Fix Wizard (described later in this article) to quickly change to the four-digit year format.

COPYING DATES IN EXCEL 97 - ANOTHER TWO DIGIT YEAR ISSUE
If you are using Excel 97, we think you'll find this interesting! Under certain circumstances, if you copy a cell that contains a date from one workbook to another, the date may unexpectedly change by 100 years - depending on whether the source workbook is open or closed. We could hardly believe this when we read it folks, but it's true!

Specifically, if the date you are copying is less than or equal to 12/31/1929, or greater than or equal to 1/1/2030, and is formatted to display as a two digit year date, you could run into this problem. If the source workbook is open, the stored date will be copied correctly; if the source workbook is closed, the displayed date is copied.

Why does this occur? In Excel 97, if you copy a date and then close the source workbook, the date on the Clipboard is converted to a text string. When you paste the text string into the destination workbook, Excel recognizes that the text string resembles a date and converts it into a date. If the copied date was formatted to show only a two-digit year, and the source workbook is closed, when you paste the date into another workbook Excel 97 treats the date as if it were entered as a two-digit year and interprets the century portion of the date and stores that information with the date. So if 1/1/1914 is displayed in the source workbook to show just 1/1/14, and you close the
source workbook before you paste the date, it will be stored as 1/1/2014 in the destination workbook. Bottom line - don't close the source workbook.

You'll find the full Knowledge Base article that discusses this is problem at:
http://support.microsoft.com/support/kb/articles/Q179/5/84.asp

If you're starting to get the idea that two-digit year dates can create all sorts of interesting problems, you're right!

A NOTE ABOUT VALID DATES
Excel 2000, 97, and Excel 98 for the Macintosh accept dates from January 1, 1900 to December 31, 9999. Valid dates in Excel 95 or 5.0 are January 1, 1900 to December 31, 2078. Dates you type, import, or copy into Excel that are before or after these date ranges are interpreted as text, not
dates.

AVOID AMBIGUITY
Although Excel allows you to enter dates that include only two parts of a date (such as month and year), the lack of a complete 3-part date forces Excel to attempt to resolve the missing component of the date. If you enter a date that contains only 2 components, Excel assumes that you are using either the Day/Month or Month/Year form of date. If Excel cannot put the date into one of these date forms, it interprets the entry as text. When a year is not supplied, Excel uses the current year. In the following examples, assume the current year is 1999:

You type Cell displays Excel stores
12/1 1-Dec 12/1/1999
1/30 30-Jan 1/30/1999
1/99 Jan-99 1/1/1999
17/01 17/01 (text) 17/01
01/17 17-Jan 1/17/1999
1/00 Jan-00 1/1/2000
1/01 1-Jan 1/1/1999

You can avoid this guessing game by entering the full date, then format the cells to display the date the way you want.

CHANGING THE DATE FORMAT - CAUTIONARY NOTE
It is possible to set the Short Date format of the Regional Settings under your Windows Control Panel to a YYYY format to always display a YYYY format. When you type a date such as 1/1/15 into an Excel cell (and other Windows applications) it displays as 1/1/2015 (or 01/01/2015
depending on the format you choose in Regional Settings).This then makes it immediately clear to you how the date entries are being interpreted. Excel 97 and 2000 will automatically expand column widths to fit the dates. In older versions of Excel a series of #### symbols may appear and you will have to manually adjust the column width if this occurs. Excel 5.0 does not have a format option for
YYYY, and you will have to create your own custom format to display the date if you are using that version of Excel.

But before you rush off to change the Control Panel setting, there is an impact (naturally) in doing so. Changing the short date format may cause problems with applications such as Visual Basic, that reads the system date as text and expect the year to be in positions 7 and 8 of the text string. If you use VB it is better to set the date format in Excel rather than through the Control Panel.

EXCEL'S DATE FUNCTIONS
There are a few functions in Excel that might cause calculation problems when we turn the century. The DATE function does not use the same century date range as the rest of Excel. This function has three arguments =DATE(year, month, day). If you use only two digits for the year, the DATE function will always assume the 20th century.
http://support.microsoft.com/support/kb/articles/Q214/3/31.asp

You may have heard about Y2K tools that cannot locate the DATE function when it was part of a uniquely defined range name (Insert, Name, Define). This problem was identified this past summer. Most of the latest versions of the Y2K tools, including Microsoft's own Date Migration Wizard
(discussed later in this article), will locate the DATE function when it is part of a user-defined range name.

The DATEVALUE function is used to convert a date represented by text into a serial number. So that if you have the entry "02/02/25", the DATEVALUE function will return the serial number for that date. If a two-digit year is used for the text date, the DATEVALUE assumes the current year, not the century date ranges discussed above.If the same workbook is loaded into different versions of Excel, you could receive different answers if the dates were calculated from text values.

HOW TO OPEN FILES AND PRECLUDE AUTOMATIC CALCULATIONS
When you open a file in Excel, any calculations are automatically updated. This includes calculations that use the NOW() and TODAY() functions. If you want to prevent this automatic calculation from occurring (perhaps so you can look at the dates in your workbook files after we turn the new century) you can disable this feature. To prevent the automatic calculations, open Excel and choose Tools, Options. On the Calculate tab mark Manual. This disables automatic calculations for all workbooks, not just the active one. The word Calculate should appear in the Status Bar.

You can either press F9 (which recalculates all open workbooks) or choose Tools, Options, Calculate tab and click the Calculate Sheet button to only recalculate the active sheet. Once you have examined your files, you can restore the automatic calculations feature in the Options dialog box.

DON'T OVERLOOK THE CODE
Have you recorded macros, written some VBA code, or adopted programs or macros from someone else, that record dates? It will be prudent of you to confirm that this code records the correct dates. For example, did you know that many custom software programs record the year portion of a date using only the last two digits of the year? See the discussion above about the potential hazards of two-digit year entries.

Even recorded macros using Excel 97, 95, 5.0 (and several versions of Excel for the Macintosh) may use the wrong date if you enter a date as part of a recorded macro. Microsoft has a Knowledge Base article for a full description.
http://support.microsoft.com/support/kb/articles/Q180/1/59.asp

Other date related coding can cause problems in the new century as well. How many weeks (full and partial) are there in a year? If you think there are 52, guess again. We stumbled across a good article entitled "54 Weeks in 2000: Another Y2K Problem!" (http://www.year2000.com/y2kcurrent1.html) that will be interesting reading whether you code in Excel or not. The gist of the article is that most programmers have used a standard of 53 weeks in a year and it turns out 2000 will have 54. Since the last time there were 54 weeks in a year was back in 1972, there are apparently a number of programs which assume that the number of weeks in a year is always 53. This may cause restrictions on data entry, improper aborts, or using invalid data.

MICROSOFT'S Y2K TOOLS
If you have Excel 97 Service Release 2 (SR2) or Excel 2000, Microsoft has developed three add-in wizards that can help you avoid some date-related worksheet problems. If you haven't installed SR2b for Office 97, there's no better time than the present. You'll find it at this site -
http://officeupdate.microsoft.com

If you have already downloaded and used Microsoft's Y2K tools, don't skip this section! We strongly encourage you to download the tools again to make sure you are using the very latest version.

The latest version is 2.0, and is designed to work with U.S. and most international versions of Excel where the primary calendar type is the Gregorian calendar. These tools will not work with the Thai and Vietnamese versions of Excel.

To download these tools, go to this article http://support.microsoft.com/support/kb/articles/Q176/9/43.asp.
Scroll down to the Installation and Use section. Click on the hyperlink to the DM2.EXE file (approx 1.9 MB) to initiate the latest download for these tools. Earlier versions of these tools were installed as separate download files, but this single executable includes all three add-in wizards.

After you download the DM2.EXE file, double-click it to install the wizard files. The date migration tools can then be accessed via the Tools menu in Excel. Installation Note - I happened to have Excel open when I installed these tools. The Date Migration command did not appear until I
closed and reopened Excel. Also, the Data Analysis add-in I had installed was "removed" from the Tools menu and replaced by the Date Migration command.

The three tools developed by Microsoft are the Date Migration Wizard, the Date Fix Wizard, and the Date Watch Wizard. The Date Migration Wizard will scan your open files, files in a specified folder (and its subfolders), or files on a network drive for dates and calculations that use or return dates with potential problems when you upgrade files to Excel 97 or Excel 2000 from an earlier
version. You can have the wizard generate a report as a result of the scan or you can perform an interactive scan. The Date Migration will even warn you if you have range names (Insert, Name, Define) that perform date functions.

If you select the report option and scan a folder, a detailed report in a new workbook is created about the scan results. It summarizes the number of files it scanned, how many files have date migration issues, how many files could not be opened or fully scanned. The report then lists the
status of each and every file it scanned. If you scan a specific workbook, a more detailed report provides additional information, such as the specific cell that may contain a problem, the formula used in that cell, and if the problem is a known problem or a potential problem.

This Date Migration wizard narrows down the 'needle in the haystack' to identify files with potential date problems, especially if you repeat the scan at the folder level and at the file level. For instance, you should first start by scanning all the files in a particular folder to generate the Folder Scan Report. Then, one-by-one, open files identified as potential date problems in the Folder Scan
Report and rerun the Date Migration Wizard to identify specific problems within each file.

The interactive scan option with the Date Migration Wizard will scan the active workbook and step you through any potential date function problems in the file. A toolbar, similar to the Auditing toolbar, is displayed during the scan. You can use this toolbar to trace cell precedents and dependents, display information about the identified function, proceed to the next function, and stop the scan.

Once you've identified the files that contain potential problems, use the Date Fix Wizard. The Date Fix Wizard includes three features: you can modify the date values so that they fall within a specific century (you get to pick the date range), you can change the date format of twodigit year dates to four-digit year dates, or you can scan a folder for workbooks with possible date problems. For
example the wizard will look for dates with a Month Year format like May-99, which will not be interpreted consistently in the year 2001 (see this article for more information about entering Month and Year dates
http://support.microsoft.com/support/kb/articles/Q180/9/52.asp).

When you run the Date Fix Wizard to change dates to a four-digit year format, a report is generated in a new workbook. The report specifies the exact worksheets and cells that were changed, along with the original cell format and new format applied. When you use the Date Fix
Wizard to scan a folder, the resulting report indicates the following information: the number of serial dates found in each workbook, the lowest and highest dates found, if any of the dates use the MMM-YY or YY-MMM date formats, and if any of the cells contain non-Gregorian formatted dates.

Once you have scanned and fixed your files, run the Date Watch Wizard to ensure any new entries, calculations or imported files don't create new Y2K problems. When the Date Watch Wizard is active, it can monitor your work in three ways: when new dates are entered they can automatically be converted to four-digit year formats, you can be alerted when a date function will calculate differently in Excel 95 and earlier versions than it will in Excel 97 or 2000, and you can have a date warning message display when you open a text file in Excel. The Date Watch Wizard is a macro. It
will disable the Undo feature of Excel when one of these three conditions is triggered.

You'll find each of these Y2K wizards easy to use. Specific instructions on using these date migration tools can be found at -
http://officeupdate.microsoft.com/2000/articles/ExY2K20Tools1.htm

LIMITATIONS OF MICROSOFT'S Y2K TOOLS
There are a few limitations with these Y2K wizards.

* The Microsoft Y2K wizards are not compatible with XLM
(Excel 4 Macro Language) programs; disable these before
running the Date Migration Tools.

* You cannot use these Y2K tools on Visual Basic for
Applications (VBA) modules. You will have to check the
code.

* These tools will not detect or correct dates that are
    part of a text string. For example, in several of my
    workbooks I want a header to reflect the last day of the
    previous week, such as "FOR THE WEEK ENDED 12/24/99". To
    accomplish this I use the following formula:

="(FOR THE WEEK ENDED "&TEXT(TODAY()+(-1-WEEKDAY(TODAY())),"mm/dd/yy")&")"

The Date Migration Wizard report identified each file I have that contains this formula, but the Date Fix Wizard will not convert the two-digit year to a four-digit year since it is part of a text string; it has to be corrected manually.

* Finally, Microsoft cannot ensure using these wizards will
make your workbooks Y2K compliant and recommends you read
their Year 2000 Readiness Disclosure (as follows):

"YEAR 2000 READINESS DISCLOSURE: ALL COMMUNICATIONS OR CONVEYANCES OF INFORMATION TO YOU CONCERNING MICROSOFT AND
THE YEAR 2000 REFLECT INFORMATION THAT IS CURRENTLY AVAILABLE AND SUBJECT TO CHANGE AT ANY TIME WITHOUT NOTICE. ALL SUCH INFORMATION IS PROVIDED TO YOU PURSUANT TO THE TERMS AND CONDITIONS SET FORTH AT MICROSOFT'S YEAR 2000 WEBSITE LOCATED AT Microsoft Year 2000 Readiness Disclosure & Resource Center
(http://www.microsoft.com/technet/year2k/).
MICROSOFT THEREFORE RECOMMENDS THAT YOU CHECK THE YEAR 2000 WEBSITE REGULARLY FOR ANY CHANGES TO THE INFORMATION CONTAINED
HEREIN. ALL SUCH INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND AND IS PROVIDED AS A YEAR 2000 READINESS DISCLOSURE."

OTHER USEFUL ONLINE ARTICLES AND SITES
If you are using Excel 97 (version 8.0) check out this
article -
http://www.microsoft.com/technet/year2k/product/user_view68320EN.htm

If you are using Excel 95 (version 7.0) check out this
article -
http://www.microsoft.com/technet/year2k/product/user_view68271EN.htm

Microsoft does have a version of the Y2K tools for Excel 98
for the Macintosh, which you can learn about in this
article -
http://support.microsoft.com/support/kb/articles/Q193/3/44.ASP

Several online e-zines have interesting Y2K coverage
including Computer Currents
http://www.computercurrents.com/advisor/y2k/index.html and
ZDNet http://chkpt.zdnet.com/chkpt/hud0007500a/www.zdnet.com/zdhelp/filters/y2k/

Return to the Excel with Excel Index