XDate Help. General Tips. About Date Format Strings. About Excel's Year-1900 Bug. Contact Information

XDate Help

Excel cannot work with dates prior to January 1, 1900. The Extended Date functions are eight new worksheet functions (written in VBA) that enable you to work with dates that range from years 0100 to 9999.

The Extended Date functions were developed by JWalk & Associates, and is freeware. The functions may be freely distributed, but it may not be sold, included with another product (including book/CD-ROM packages), or modified in any way.

General Tips

·  Beware of Calendar Changes. Be careful if you use dates prior to 1752. Differences between the historical American, British, Gregorian, and Julian calendars can result in inaccurate computations.

·  Use 4-Digit Dates. It is highly recommended that you always use 4-digit dates with the Extended Date functions. Failure to do so may result in erroneous results.

About Date Format Strings

The XDATE and XDATEADD functions use an optional fmt argument. If omitted, the date is formatted according to your system's "short date" format (as specified in the Windows control panel).  Listed below are examples of some date format strings you may find useful. Make sure you put the date format string in quotation marks.

Using the Insert Function dialog

You can enter any of the Extended Date functions directly into your formula, or use Excel's Insert Function dialog box. The Extended Date functions are listed in the 'Date & Time' function category (all of the functions begin with the letters 'XDATE').

About Excel's Year-1900 Bug

Excel incorrectly assumes that the year 1900 is a leap year. Even though there was no February 29, 1900. Excel accepts the following formula, and displays the result as the 29th day of February, 1900.

=DATE(1900,2,29)

The Extended Date functions do not have this erroneous behavior. The formula below correctly interprets the date as March 1, 1900.

=XDATE(1900,2,29)

Because of this Excel bug, you must use caution when using the Extended Date functions with an argument that's an actual Excel date. In particular, the XDATEDIF function will return the wrong value if either of its arguments is an Excel date between January 1, 1900 and February 29, 1900. The result will be off by one day (add one to the result to get the correct answer).

For more information about Excel's Year-1900 bug, refer to the following Web document:

http://support.microsoft.com/kb/214058/en-us

Returns a specified date, displayed using the optional ftm date format string.

Syntax:

=XDATE(y,m,d,fmt)

y             A 4-digit year in the range 0100 to 9999 m            A month number (1-12) d             A day number (1-31) fmt         Optional. A date format string

If the fmt argument is omitted, the date is displayed using the system's "short date" setting (as specified in the Windows Control Panel).

If the m or d argument exceeds a valid number, it "rolls over" into