Working with dates

You can use a string variable to store a date, of course, but you can't perform date calculations on one. Using the Date data type is a better way to work with dates.

A variable defined as a date uses 8 bytes of storage and can hold dates ranging from January 1, A.D 100 to December 31, 9999. That's a span of nearly 10,000 years — more than enough for even the most aggressive financial forecast! The Date data type is also useful for storing time-related data. In VBA, you specify dates and times by enclosing them between two hash marks (#), as shown next.

The range of dates that VBA can handle is much larger than Excel's own date range, which begins with January 1, 1900. Therefore, be careful that you don't attempt to use a date in a worksheet that is outside of Excel's acceptable date range.

Here are some examples of declaring variables and constants as Date data types:

Dim Today As Date Dim StartTime As Date Const FirstDay As Date = #1/1/2001# Const Noon = #12:00:00#

Date constants are always defined using month/day/year format, even if your system is set up to display dates in a different format (for example, day/month/year).

If you use a message box to display a date, it will be displayed according to your system's short date format. Similarly, a time is displayed according to your system's time format (either 12- or 24-hour). You can modify these system settings by using the Regional Settings option in the Windows Control Panel.

About Excel's Date Bug

It is commonly known that Excel has a date bug: It incorrectly assumes that the year 1900 is a leap year. Even though there was no February 29, 1900, Excel accepts the following formula and displays the result as the 29th day of February, 1900:

VBA does not have this date bug. The VBA equivalent of Excel's DATE function is DateSerial. The following expression (correctly) returns March 1, 1900:


Therefore, Excel's date serial number system does not correspond exactly to the VBA date serial number system. These two systems return different values for dates between January 1, 1900 and February 28, 1900.

The companion CD-ROM contains an Excel add-in that I created called Extended Date Functions. This add-in, which was created with VBA, adds new worksheet functions to Excel.These new functions enable you to create formulas that work with dates prior to January 1,1900.

Was this article helpful?

0 0

Post a comment