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.

Search JabSto ::

Custom Search