Willow Tips

Tip #1999-12: Using dates in Excel

Date audit tips for Excel

Year 2000 concerns brought to most people's attention the fact that dates require special handling. Here are a couple of quick tips for you to verify that your workbooks will work smoothly with dates.

The most important, and easiest, thing you can do is display your dates in four digit format; e.g., 1/1/2000 instead of 1/1/00. In the former example, we know exactly which year is meant. In the latter case, it could be 2000 and it could be 1900.

To change your dates to four digit format, select the cells containing dates, then go to Format Cells, click on the Date option in the list box and select a format that contains yyyy. (Excel5.0c users: you'll have to create your own custom format. Here's how)

How Excel determines a date

Dates are not saved the way you type them into the cell. Underneath all the formatting, Excel saves your date as a serial number. The serial number is the number of days elapsed since a particular start date. In Excel for Windows, the start date is January 1, 1900. In Excel for Macintosh, the start date is Jan. 1, 1904. (Beware, if you're converting sheets from Macintosh to Windows!). Different versions of Excel have different behavior. For example, Excel 4, 5, and 7 support dates up to 2078. Excel 97 supports dates up to 9999.

If you insist on using 2 digit years (e.g., 12/31/98), understand exactly what that means to Excel. Excel interprets what you type in based on an assumption window. It takes the two digits you type, assigns them to a century, then saves the associated serial number. Unfortunately, different versions of Excel have different assumption windows. If you type in a year of 23, Excel97 assumes you mean the 2023; Excel 5 and 7 assume that you mean 1923. So even though you see 2/28/23 on your worksheet, the underlying serial number is completely different. And any calculations subsequently made on those cells will be different. The easiest way to avoid this is to use 4 digit dates. It'll cost you two extra keystrokes now, but may save you hours of frustration tracking down the source of unexpected calculations!

But if you really want to use 2 digit dates, the assumption window summary is below:

The strange case of the =DATE function

Just to keep things interesting, the =DATE function performs a little differently from the rest of Excel. It takes three parameters; year, month and day. If you specify all four digits for the year, you get all possible years up to Excel's maximum. If you use just 2 digits, you get only 19xx -- NO interpretation window at all. For example, if you enter =DATE(19,12,31), you will get the serial number for December 31, 1919. The usual interpretation window would have given you December 31, 2019.

VBA caveats

If you use Visual Basic for Applications or any legacy macro code, you need to be very careful to check these applications for date field problems. You could have calculations going on in the background that will yield unpredictable results come January 1, 2000.

Online Y2K resources

Microsoft itself has a tremendous body of Year 2000 information on their website. Go to http://www.microsoft.com and use the search option.