Microsoft Office Tutorials and References

In Depth Information

**Extended Date Functions**

By the way, I hope you understand that the point of this example is
not
to create a new SUM

function. Rather, it demonstrates how to create custom worksheet functions that look and work

like those built into Excel.

Extended Date Functions

A common complaint among Excel users is the inability to work with dates prior to 1900. For

example, genealogists often use Excel to keep track of birth and death dates. If either of those

dates occurs in a year prior to 1900, calculating the number of years the person lived isn’t possible.

I created a series of functions that take advantage of the fact that VBA can work with a much

larger range of dates. The earliest date recognized by VBA is January 1, 0100.

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.

The functions are

h
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.

h
XDATEADD(xdate1,days,fmt):
Adds a specified number of days to a date. As an option,

you can provide a date formatting string.

h
XDATEDIF(xdate1,xdate2):
Returns the number of days between two dates.

h
XDATEYEARDIF(xdate1,xdate2):
Returns the number of full years between two dates

(useful for calculating ages).

h
XDATEYEAR(xdate1):
Returns the year of a date.

h
XDATEMONTH(xdate1):
Returns the month of a date.

h
XDATEDAY(xdate1):
Returns the day of a date.

h
XDATEDOW(xdate1):
Returns the day of the week of a date (as an integer between 1 and

7).

Figure 10-8 shows a workbook that uses some of these functions.

Keep in mind that the date returned by these functions is a
string
, not a real date. Therefore, you

can’t perform mathematical operations on the returned value using Excel’s standard operators.

You can, however, use the return value as an argument for other Extended Date functions.