Extended Date Functions
A companion file is available: Click here to download
Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. I create an add-in that addresses this deficiency. The Extended Date Functions add-in (XDate) 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).
These are all VBA functions.
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.
The XDate add-in requires Excel 97 or later.
Be careful if you use dates prior to 1752. Differences between the historical American, British, Gregorian, and Julian calendars can result in inaccurate computations.
My Power Utility Pak 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.
Complete context-sensitive online help is included.
Installation is a two-step process:
- Extract the files
Download and execute the xdate.zip file. Extract the files into any directory.
- Install the add-in
Start Excel 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 has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks