Microsoft Office Tutorials and References
In Depth Information
Many users are surprised to discover that Excel can't work with dates prior to the year 1900. To correct this de-
ficiency, I created a series of extended date functions. These functions enable you to work with dates in the
years 0100 through 9999.
The extended date functions are
• XDATE(y,m,d,fmt): Returns a date for a given year, month, and day. As an option, you can provide a date
• 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 calcu-
• 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).
Figure 26-6 shows a workbook that uses a few of these functions.
Figure 26-6: Examples of the extended date function.