# Worried over Y2K? Now here's a Y1.9K problem

With all the talk about the year 2000 problem, you may not be aware that Excel also suffers from a year 1900 (Y1.9K) problem.

People who use Excel to store historical information often need to work with dates earlier than January 1, 1900. The only way to create a date such as July 4, 1776, in Excel is to enter it into a cell and have the program interpret it as text. Unfortunately, you can't manipulate dates stored as text - if you want to alter their formatting, for example, or if you need to calculate the day of the week they fell on.

To address this problem, I created an add-in (for Excel 97 or later versions) called Extended Date Functions. With this add-in installed, you'll have access to eight new worksheet functions that let you work with dates in any year from 0100 to 9999. You can download a free copy of it from www.j-walk.com, or from our cover CD of the October 1999 edition.

The new functions are:

XDATE(y,m,d,fmt): returns the specified date (as text) in the format specified by the fmt format string (optional).

XDATEADD(date1,day,fmt): returns the date (as text) that is day number of days after date1 in the format specified by the fmt format string (optional).

XDATEDAY(date1): returns the unique day number for a date.

XDATEDIF(date1,date2): returns the number of days between two dates.

XDATEDOW(date1): returns an integer corresponding to the day of the week.

XDATEMONTH(date1): returns the month number for a date.

XDATEYEAR(date1): returns the four-digit year for a date.

XDATEYEARDIF(date1,date2): returns the number of full years between two dates; useful for calculating ages.

Both XDATE and XDATEADD functions return a text string. You can't use Excel's date formats with this string, but you can provide a format string as an argument for the function. For example, the formula below adds five days to December 1, 1895 and displays the result as 'Dec-06-1895' (these functions use standard Excel format strings):