VBA Date Variables: How to Declare and Use Them

In VBA, declare a date variable with Dim eventDate As Date. A Date variable can hold a calendar date, a time, or both; use it for date calculations and comparisons rather than keeping dates as text.

What the VBA Date type represents

Date is a built-in VBA data type for date-and-time values. It uses an eight-byte floating-point representation based on OLE Automation dates: the whole-number part represents days and the fractional part represents time within a day. For example, half a day represents noon. VBA supports dates from January 1, 0100, through December 31, 9999, subject to the valid range for the result of a calculation.

A date variable is not limited to a date without a time. If you assign a value that includes hours and minutes, those remain part of the value even if the cell or message displaying it shows only the calendar date. This distinction matters when comparing values: two values that look like the same date may differ because one includes a time.

The word Date also names a VBA function that returns the computer’s current system date. Context tells VBA whether you mean the data type, as in As Date, or the function, as in today = Date.

How to declare and assign a Date variable

Use Dim to declare the variable, then assign a valid date value. Explicitly declaring the type helps VBA catch incompatible assignments and makes the code’s intent clearer than storing dates in a general-purpose Variant.

Sub ShowDateBasics()
    Dim eventDate As Date
    Dim today As Date

    ' Build a date from year, month, and day components.
    eventDate = DateSerial(2026, 11, 2)
    today = Date

    MsgBox "Event date: " & Format$(eventDate, "yyyy-mm-dd")
End Sub

DateSerial is a dependable way to construct a date from numeric components. It avoids asking VBA to interpret a string such as "11/2/2026", whose meaning can depend on regional date settings. Use a four-digit year in examples and in code that builds dates from external inputs.

A declared Date variable is not an optional or nullable value: VBA does not give it a special “no date” state. A variable that has not been assigned a meaningful date still has its default value, so set it deliberately before using it. If a field may genuinely be missing, consider a Variant and test for an appropriate missing value, or use a separate Boolean flag to record whether a date has been supplied.

Getting today’s date and the current time

Choose the built-in function that matches the value you need:

Date returns today’s date according to the computer’s system clock, with no meaningful current time component.
Time returns the current time of day.
Now returns both the current date and time.

For example, Dim startedAt As Date: startedAt = Now records a timestamp, while Dim reportDay As Date: reportDay = Date records a date for a daily report. The computer’s clock and regional settings can affect what a person sees, so use a trusted time source if a process requires an authoritative timestamp. VBA’s Date is not the same thing as a dedicated UTC timestamp.

To assemble a value from separate fields, use DateSerial(year, month, day). For a time, use TimeSerial(hour, minute, second). These functions also handle out-of-range component values by normalizing them into adjacent units, which can be useful in deliberate calculations but can hide bad input if the components were expected to be strictly valid. Validate user-entered parts before constructing the final value.

Date calculations and comparisons

Date values support ordinary arithmetic. Adding or subtracting a number changes the value by that many days, and subtracting one Date value from another produces the elapsed difference in days, potentially including a fractional day. For example, adding 7 to a date gives a date one week later. Prefer DateAdd when the intended unit is clearer as weeks, months, or years:

Sub CheckDeadline()
    Dim startDate As Date
    Dim deadline As Date

    ' Use a fixed example date so the result is repeatable.
    startDate = DateSerial(2026, 11, 2)
    deadline = DateAdd("d", 14, startDate)

    If deadline < Date Then
        MsgBox "The deadline has passed."
    End If
End Sub

In this example, the calculated deadline is November 16, 2026. The comparison with Date is a date-only comparison because the deadline was created at midnight. If a deadline contains an afternoon time, comparing it with Date can treat it as later than the current date’s midnight even when that afternoon has already passed. Use Now when the comparison should include the current time.

DateDiff is useful when you want a count of intervals between two values, but its result is not always the same as elapsed time divided by a unit. For example, DateDiff("d", startDate, endDate) counts day boundaries crossed. When calculating elapsed hours or fractional days, subtract the Date values directly and interpret the result accordingly. DateAdd("m", 1, someDate) adds a calendar month; it is not equivalent to adding 30 days, and month-end dates can produce results in the following month’s valid range.

Converting and displaying dates safely

Keep a date as a Date value while calculating or comparing it. Convert to text only when preparing output. Format$ returns a formatted string; for example, Format$(eventDate, "yyyy-mm-dd") produces a year-month-day display. In VBA formatting strings, use nn for minutes in a time format, because mm is used for months in date contexts. A combined format can be "yyyy-mm-dd hh:nn:ss".

CDate and DateValue can convert text or other values into dates, but interpreting a text date can depend on Windows regional settings. A string like "03/04/2026" could mean March 4 or April 3, depending on the locale. IsDate can help determine whether VBA recognizes an expression as a date, but it does not make ambiguous text unambiguous. For imported data, establish the source format and parse its components explicitly rather than relying on a user’s regional settings.

Formatting changes how a value is displayed, not the underlying Date value. If a cell shows only a date, a time portion may still be present and can affect comparisons. Conversely, converting a date to a text string can make sorting and arithmetic unreliable. Preserve typed values for operations and apply a display format at the point where the value is shown.

Using Date values in Excel worksheets

Excel worksheet dates are stored as numbers with formatting applied, while VBA uses an OLE Automation date representation. When VBA reads or writes date values through Excel’s object model, Excel handles the conversion for the workbook. Do not assume that a raw numeric serial has identical meaning across every workbook: Excel supports both the 1900 and 1904 date systems, and the serial associated with a displayed date can differ between them.

There is also a historical compatibility difference: Excel’s 1900 date system treats 1900 as if it had a February 29, even though that date did not exist. VBA’s Date type does not provide February 29, 1900 as a valid calendar date. For ordinary modern dates, assigning a Date value to a worksheet cell is usually simpler and safer than manually calculating or transferring serial numbers.

Sub WriteReportDate()
    Dim reportDate As Date
    Dim target As Range

    ' Replace A1 with the intended destination before running.
    reportDate = Date
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    target.Value = reportDate
    target.NumberFormat = "yyyy-mm-dd"
End Sub

This example assumes the workbook contains a worksheet named Sheet1; change the sheet name or destination to match your file. The cell’s number format controls its appearance, while its value remains a date that Excel can use in calculations. Before running a macro that changes a workbook, save a copy: VBA macros cannot be undone with Ctrl+Z. Excel for the web can open macro-enabled workbooks but cannot run VBA; use desktop Excel for these procedures. VBA is also available in desktop Excel for Mac, although platform-specific code and some editor details can differ.

Common mistakes and how to avoid them

Using a string as the working value: Text dates can be ambiguous and do not behave as consistently as Date values in arithmetic. Convert or parse the input once, then work with a typed date.
Confusing display with value: A cell formatted as a date can hide a time fraction. Inspect or format the full value if a comparison gives an unexpected result.
Using Date when time matters: It returns the current date, not the current timestamp. Choose Now for date-and-time comparisons.
Assuming a month is 30 days: Use calendar-aware operations such as DateAdd("m", 1, value) for a month later, and decide how month-end cases should be handled.
Trusting an imported serial without context: A number may be an Excel date serial, an identifier, or a value from a different date system. Confirm what the source represents before converting it.

For dates assembled from user input, validate both the components and the resulting value. A month of 13, for instance, may be normalized into the next year by DateSerial rather than rejected. That behavior is convenient for intentional date arithmetic, but it is not a substitute for checking whether the user entered a valid calendar date.

Frequently Asked Questions

How do I declare a date variable in VBA?
Write Dim myDate As Date inside a procedure or at the appropriate module scope. Assign it a date before relying on its value.

What is the difference between Date and Now in VBA?
Date returns the current system date, while Now returns the current system date and time. Use Time when you need only the current time of day.

How do I set a specific date without a locale problem?
Use DateSerial(year, month, day), such as DateSerial(2026, 11, 2), rather than relying on VBA to interpret an ambiguous date string.

Can a VBA Date variable store a time?
Yes. A Date value can contain both date and time. Use Now for the current timestamp or TimeSerial to construct a time component.

Why does a date comparison give an unexpected result?
The values may contain different time fractions, even if they display as the same date. They may also have been parsed under different regional settings or converted from worksheet serials incorrectly. Format or inspect the full values and verify how each was created.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.