 Image copyright (c) 1998 by PhotoDisc, Inc.  Used under license. |
Data Sharing Methods: Finding & Resolving Y2K Problems |
)
Overview: About Y2K problems when sharing data |
Y2K-related problems can occur under some circumstances when:
Many of these types of problems result from the use of
dates with two-digit years, such as 05/13/29,
in the data that you're sharing.
Such "century-ambiguous" dates can be varyingly interpreted
by different programs as falling within either the 20th or 21st centuries.
For example, the date 05/13/29 might be interpreted as either
May 13, 1929 or May 13, 2029.
As a result, your dates could actually change centuries:
If you share important data containing century-ambiguous dates via any of the following methods, you will need to closely examine the methods you use for potential Y2K-related problems:
Finding Y2K problems in data sharing methods |
To find situations where you'll need to check data sharing for potential Y2K problems, ask yourself three questions:
Any data that is exported by one program and imported by another program - whether by
sharing data via text files or other 'intermediate' files,
copying and pasting via the Clipboard,
or through programming instructions that send data between
two programs - is potentially
at risk of Y2K problems if that data contains century-ambiguous dates, such as
05/13/29.
Microsoft's "Checking Data Import/Export Routines" section of its white paper,
Preparing Office Solutions for the Year 2000
points out that:
Each case [where data is exported or imported] needs to be verified to ensure:
For exports, the full four digits of the year are being output.
For imports, all four digits of the year are being retrieved, or in the case where the input file has only two digit years, the correct century is being assumed.
Here's an example of what can happen when data containing century-ambiguous two-digit dates is exported by one program and imported by another program:
You have a Microsoft Excel spreadsheet file containing dates. You save this spreadsheet as a tab-delimited or comma-delimited [CSV] text file so that the spreadsheet's data can be used by a database program.
Dates in the Excel spreadsheet which are
displayed with century-ambiguous two-digit years (e.g. 05/13/29)
will also be exported using that format.
For instance, a cell in the spreadsheet may internally be stored as a serial date
representing "May 13, 2029", but
the cell might display this date in a shorter format, as 05/13/29.
If you save that spreadsheet
as a text file, only the century-ambiguous displayed date, 05/13/29 - not the
full stored date, 05/13/2029 - will be saved to the text file.
Another program reading this text file might interpret this date as falling into a different century than your Excel program does. Thus, this date might actually change centuries as a result of being exported to a text file by one program and imported from that text file by another program.
For example, if you later import this text file into a FileMaker Pro database
using FileMaker's "Import Record" function, the date 05/13/29
will be interpreted as May 13, 1929. That's because FileMaker
assumes all imported dates with only two digit years fall within the 1900s.
Thus, as a result of a century-ambiguous date being exchanged via a text file, a date that was
originally stored as May 13, 2029 in a cell in an Excel spreadsheet was changed to
May 13, 1929 when it was imported into a record in a FileMaker database
Any dates stored as text, numbers, or other non-date formats within data files, such as spreadsheets and database files, could cause problems when these files are "passed around": when they are saved by one program and then opened by another program. These programs often will occur when these 'non-date dates' are converted to 'real' dates using built-in date functions, user-defined functions, or custom macros or scripts. (This is likely to be a less-common problem than the one described above, but it could potentially affect some UC Berkeley campus users of certain spreadsheet and database programs.)
Here's an example: You've created a spreadsheet using
Microsoft Excel 95 for Windows or Microsoft Excel 5.0 for the Macintosh.
One of the spreadsheet's cells contains a simple formula using one of
Excel's built-in
date functions, =DATEVALUE("05/13/20"), that converts the text string
"05/13/20" to a date and displays it.
In Excel 95 and 5.0, this cell would contain a date like "05/13/1920" or "May 13, 1920". However, if you sent this spreadsheet as an e-mail attachment to someone who opened it using Excel 97 or 98, this cell would now contain a date like "05/13/2020" or "May 13, 2020". The date in this spreadsheet cell has changed centuries simply because the spreadsheet was opened in a different version of Excel!
This is because Microsoft Excel 95 for Windows and
Excel 5.0 for the Macintosh consider only two-digit years from "00"
through "19" as falling into the 21st Century.
However, Microsoft Excel 97 for Windows and Excel 98 for the Macintosh,
consider dates with two-digit years in the range of "00" through "29"
as falling into the 21st Century. As a result, when the
=DATEVALUE() function is recalculated, a text date ending in the year "20"
would change centuries: from 1920 in earlier versions of Excel, to 2020 in more
recent versions.
Resolving Y2K problems in data sharing methods |
|
Before installing any updates or patches which may alter your computer's operating system
software or application programs, and before making any changes to your critical
documents (data files), be sure that you have a complete, current backup
of your computer's data.
For general backup advice, see the Berkeley Computing and Communications articles Ask Dr. Micro: How can I back up the files on my computer and Simplify your life with the UCBackup service .
|
The first step in resolving the problem is to pool your expertise with that of the people with whom you regularly exchange data, and work together to identify all of the situations in which you're sharing date-containing data. Then, you'll need to look at each situation to determine if century-ambiguous dates are being exported and imported, or if files containing 'dates' stored as text or numbers are being exchanged.
Most
automated software tools
that scan data files to detect date-related
problems can help you flag potential problems when sharing data via these files.
Often, they may also be able to suggest how to change your data files
If you're using a custom-written or highly-customized application program,
you'll need to check that program's date-processing routines carefully to see how it handles
century-ambiguous dates. See
Custom Applications: Finding & Resolving Y2K Problems
for suggestions
about how to do so.
If you're using commercial off-the-shelf application programs to share important data, you'll need to have at least a basic understanding of how those program handle dates when importing or exporting data. It's particularly useful to know how such programs assign century-ambiguous dates with two-digit years to either the 20th or 21st Centuries. You can usually find this information in your vendors' Y2K compliance statements, although sometimes it might also be presented in your program's user documentation. For suggestions on how to locate Y2K compliance statements for the commercial application programs you use, see:
Commercial Off-the-Shelf Applications: Resources for Identifying Y2K Compliance
and
Y2K Compliance Status of Selected Off-the-Shelf Applications Used at UC Berkeley
If data containing century-ambiguous dates are being shared between different programs, and these programs use different rules for assigning these dates to the 20th or 21st century, you'll need to change your data sharing practices to always exchange dates with full, unambiguous four-digit years.
This may involve reformatting dates in your data files so they are displayed with full four-digit years (which might also require other changes, such as expanding your spreadsheet columns to accommodate the now-longer dates), or replacing 'dates' stored as text or numbers within your files with true dates.
If you've written custom macros, scripts, or other programming code to exchange data, you may also need to revise that code so that it correctly exports and/or imports dates with full four-digit years.