March 6, 2011, 7:18 p.m.
posted by dante
Working with Date Expressions
A date expression is an expression that returns a Date value. For operands in date expressions, you can use either a variable declared as Date or a date literal. For the latter, you enclose the date in pound signs, like so:
dateVar = #8/23/04#
When working with dates, it helps to remember that VBA works with dates internally as serial numbers. Specifically, VBA uses December 31, 1899 as an arbitrary starting point and then represents subsequent dates as the number of days that have passed since then. So, for example, the date serial number for January 1, 1900 is 1, January 2, 1900 is 2, and so on. Figure displays some sample date serial numbers.
Similarly, VBA also uses serial numbers to represent times internally. In this case, though, VBA expresses time as a fraction of the 24-hour day to get a number between 0 and 1. The starting point, midnight, is given the value 0, noon is 0.5, and so on. Figure displays some sample time serial numbers.
The advantage of using serial numbers in this way is that it makes calculations involving dates and times very easy. Because a date or time is really just a number, any mathematical operation you can perform on a number can also be performed on a date. This is invaluable for procedures that track delivery times, monitor accounts receivable or accounts payable aging, calculate invoice discount dates, and so on.
VBA also comes equipped with quite a few date and time functions. Figure summarizes them all.
Listing 4.4 shows a couple of procedures that take advantage of a few of these date functions.
Function CalculateAge(birthDate As Date) As Byte Dim birthdayNotPassed As Boolean birthdayNotPassed = CDate(Month(birthDate) & "/" & _ Day(birthDate) & "/" & _ Year(Now)) > Now CalculateAge = Year(Now) - Year(birthDate) + birthdayNotPassed End Function ' ' Use this procedure to test CalculateAge. ' Sub TestIt2() MsgBox CalculateAge(#8/23/59#) End Sub
The purpose of the CalculateAge function is to figure out a person's age given the date of birth (as passed to CalculateAge through the Date variable named birthDate). You might think the following formula would do the job:
Year(Now) - Year(birthDate)
This works, but only if the person's birthday has already passed this year. If the person hasn't had his or her birthday yet, this formula reports the person's age as being one year greater than it really is.
To solve this problem, you need to take into account whether or not the person's birthday has occurred. To do this, CalculateAge first declares a Boolean variable birthdayNotPassed and then uses the following expression to test whether or not the person has celebrated his or her birthday this year:
CDate(Month(birthDate) & "/" & Day(birthDate) & "/" & Year(Now)) > Now
This expression uses the Month, Day, and Year functions to construct the date of the person's birthday this year and uses the CDate function to convert this string into a date. The expression then checks to see if this date is greater than today's date (as given by the Now function). If it is, the person hasn't celebrated his or her birthday, so birthdayNotPassed is set to True; otherwise, birthdayNotPassed is set to False.
The key is that, to VBA, a True value is equivalent to -1, and a False value is equivalent to 0. Therefore, to calculate the person's correct age, you need only add the value of birthdayNotPassed to the expression Year(Now) - Year(birthDate).