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 4.8 displays some sample date serial numbers.

Table 4.8 Examples of Date Serial Numbers

Serial Number



December 31, 1900


June 6, 1944


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.

Table 4.9 Examples of Time Serial Numbers

Serial Number



6:00:00 AM


9:00:00 AM


5:00:00 PM


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 4.10 summarizes them all.

0 0

Post a comment