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/2007#
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.
Table below displays some sample date serial numbers.
Examples of Date Serial Numbers VBA's String Functions| Serial Number | Date |
| 366 | December 31, 1900 |
| 16229 | June 6, 1944 |
| 39317 | August 23, 2007 |
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. Table 4.9 displays some sample time serial numbers.
Examples of Time Serial Numbers VBA's String Functions| Serial Number | Date |
| 0.25 | 6:00:00 AM |
| 0.375 | 9:00:00 AM |
| 0.70833 | 5:00:00 PM |
| .99999 | 11:59:59 PM |
You can combine the two types of serial numbers. For example, 39317.5 represents 12 noon on August 23, 2007.
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. Table below summarizes them all.
VBA's Date and Time Functions| Function | Returns |
| CDate(expression) | Converts expression into a Date value. |
| Date | The current system date, as a Variant. |
| Date$() | The current system date, as a String. |
| DateAdd(interval,number,date) | A Date value derived by adding the specified number of intervals (days, months, years, and so on) to date. |
| DateDiff(interval,date1,date2) | A numeric value that represents the number of intervals (days, months, years, and so on) between date1 and date2. |
| DatePart(interval,date) | A numeric value that corresponds to the part of date specified by interval (the day, month, year, and so on). |
| DateSerial(year,month,day) | A Date value for the specified year, month, and day. |
| DateValue(date) | A Date value for the date string. |
| Day(date) | The day of the month given by date. |
| Hour(time) | The hour component of time. |
| Minute(time) | The minute component of time. |
| Month(date) | The month component of date. |
| MonthName(month) | The name of the month. |
| Now | The current system date and time. |
| Second(time) | The second component of time. |
| Time | The current system time, as a Variant. |
| Time$ | The current system time, as a String. |
| Timer | The number of seconds since midnight. |
| TimeSerial(hour,minute,second) | A Date value for the specified hour, minute, and second. |
| TimeValue(time) | A Date value for the time string. |
| Weekday(date) | The day of the week, as a number, given by date. |
| WeekdayName(weekday) | The name of the weekday. |
| Year(date) | The year component of date. |
Listing below shows a couple of procedures that take advantage of a few of these date functions.
A Function Procedure That Uses Various Date Functions to Calculate a Person's AgeFunction 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 whether 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).