XDate: Extended Date Functions Add-In

Return to The Spreadsheet Page

Excel page

Excel files

Description:

Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. The Extended Date Functions add-in (XDate) corrects this deficiency, and allows you to work with dates in the years 0100 through 9999.

When the XDate add-in is installed, you can use any of the following new worksheet functions in your formulas:

  • XDATE(y,m,d,fmt): Returns a date for a given year, month, and day. As an option, you can provide a date formatting string.
  • XDATEADD(xdate1,days,fmt): Adds a specified number of days to a date. As an option, you can provide a date formatting string.
  • XDATEDIF(xdate1,xdate2): Returns the number of days between two dates.
  • XDATEYEARDIF(xdate1,xdate2): Returns the number of full years between two dates (useful for calculating ages).
  • XDATEYEAR(xdate1): Returns the year of a date.
  • XDATEMONTH(xdate1): Returns the  month of a date.
  • XDATEDAY(xdate1): returns the day of a date.
  • XDATEDOW(xdate1): Returns the day of the week of a date (as an integer between 1 and 7).

NEW - Thanks to J.E. McGimpsey, a Macintosh version
is now available (scroll down to download it)

Applications:

The XDate add-in is particularly useful for genealogists and others who need to perform simple calculations using pre-1900 dates. The figure below, for example, shows the XDATEYEARDIF function being used to calculate ages.

Requirements:

The XDate add-in requires Excel 97 or later. A version for Excel/Macintosh is also available.

Limitations:

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

Note:

PUP v5 also includes the XDATE functions. However, they are not packaged in an add-in. Rather, you can add the functions directly to the VBA project for your workbook. As a result, you can distribute the workbook without a dependent add-in.

Free Download:

This add-in is fully functional, not crippled, and has no nag messages. It's absolutely free, with no strings attached. Click the link below to go to the download page.

Documentation:

Complete context-sensitive online help is included (Windows version only).

Excel For Windows Installation:

Installation is a two-step process:

  1. Extract the files
    Download and execute the xdate.exe file to extract the XDate files. You can put the files into any directory. 
  2. Install the add-in
    Start Excel 97 (or later version) and select the Tools - Add-Ins command. In the Add-Ins dialog box, click the Browse button and locate xdate.xla (the file you extracted in Step #2). Click OK

You can type the functions manually, or use Excel's Paste Function dialog box. To access the Paste Function dialog, click the Paste Function button, or select Insert - Function.  The XDate Functions are listed in the 'Date & Time' Category. When a function is selected in the Paste Function dialog, press F1 to read the online help.

Excel for Macintosh Installation

Installation is a three-step process:

  1. Extract the files
    Download the StuffIt file to your computer. Use StuffIt Expander  to expand the file. The resulting folder will contain the add-in, the Help document, a read-me text file and a license.
  2. Copy to Your Add-Ins Folder
    Move the xdate.xla add-in to your Add-Ins folder (usually in the Office:Add-Ins folder within your Applications:Microsoft Office folder, although you may put it elsewhere).
  3. Install the add-in
    Start Excel and select the Tools - Add-ins command. In the Add-Ins dialog box, click the Select button and locate xdate.xla (the file you moved in Step #2). Select the file, click Open, then click OK.

Note:
On-line Help is not available in the Mac version. Refer to the "XDate Help.doc" help document included in the download.

Technical Support:

To report a problem with the XDate add-in for Windows, contact J-Walk & Associates via email.