Date Delimiters

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

When you work with date literals in your code, you must indicate to Microsoft® Visual Basic® for Applications (VBA) that a value is a date. If you do not, VBA might think you are performing subtraction or floating-point division.

For example, if you run the following fragment, the value that VBA assigns to the Date variable is not April 5, 1998, but 4 divided by 5 divided by 98. Because you are assigning it to a Date variable, VBA converts the number to a date, and prints "12:11:45 AM" to the Immediate window:

Dim dteDate As Date
dteDate = 4 / 5 / 98
Debug.Print dteDate

To avoid this problem, you must include delimiters around the date. The preferred date delimiter for VBA is the number sign (#). In addition, you can use double quotation marks, as you would for a string, but doing so requires VBA to perform an extra step to convert the string to a date. If you rewrite the fragment as follows to include the date delimiter, VBA prints "4/5/98" to the Immediate window:

Dim dteDate As Date
dteDate = #4/5/98#
Debug.Print dteDate

See Also

Working with Dates and Times | The Date Data Type | Getting the Current Date and Time | Formatting a Date | Assembling a Date | Getting Part of a Date | Adding and Subtracting Dates | Calculating Elapsed Time