Excel VBA Date and Time Variable Types

In Excel VBA, use the Date data type for a calendar date, a time, or a date-and-time value; it stores all three in one variable. Use Double for an elapsed duration when you need to calculate or accumulate time spans, and use Variant only when you need a value that may or may not be a date.

What the VBA Date type represents

A VBA Date variable can hold a date alone, a time alone, or both together. For example, a date at midnight and a timestamp at 2:30 p.m. are both valid Date values. You do not need separate date and time variable types. The same type is used for values returned by built-in functions such as Date, Time, and Now.

VBA represents Date values as an eight-byte floating-point number compatible with OLE Automation dates. The whole-number portion represents a day, while the fractional portion represents a part of a day. Consequently, adding one to a Date advances it by one day, and adding 0.5 advances it by twelve hours. You normally do not need to manipulate that underlying number yourself: use date functions and ordinary date arithmetic instead.

The Date type has a defined range, from January 1, 100 through December 31, 9999. It is not a text string and is not merely a display format. A variable can contain a Date value even if it is later displayed in a different format in the Immediate window or a worksheet.

Declaring and assigning date-time variables

Declare a variable explicitly with As Date. The following procedure records the current date and time, then prints it in a readable form. In a VBA format string, nn represents minutes; mm represents a month in a date context.

Sub ShowCurrentTimestamp()
    Dim recordedAt As Date

    ' Capture the computer's current date and time.
    recordedAt = Now
    Debug.Print Format$(recordedAt, "yyyy-mm-dd hh:nn:ss")
End Sub

Now returns the computer’s current system date and time. Date returns the current date with the time at midnight, while Time returns the current time with the date component set to the current system date. These functions read the system clock; they do not provide a time-zone-aware timestamp.

Before running a macro that changes workbook data, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. This example only prints to the Immediate window, but the same precaution is sensible when adapting code for a workbook.

Building reliable dates and times

For dates assembled from separate year, month, and day values, DateSerial is usually clearer and safer than joining text. For a time assembled from hour, minute, and second values, use TimeSerial. Both return date/time values that can be assigned to a Date variable.

Sub BuildAppointment()
    Dim appointment As Date

    ' Construct November 2, 2026 at 2:30 p.m.
    appointment = DateSerial(2026, 11, 2) + TimeSerial(14, 30, 0)
    Debug.Print Format$(appointment, "yyyy-mm-dd hh:nn")
End Sub

The example produces November 2, 2026 at 14:30. Passing four-digit years avoids the special interpretation VBA applies to year arguments from 0 through 99. DateSerial also normalizes out-of-range month and day values—for example, a day value beyond the end of a month can roll into the next month. That behavior can be useful, but validate input first if an invalid day should instead be rejected.

VBA also supports date literals enclosed in number signs, but typing dates into code can invite regional-format confusion and makes values less convenient to change. For dates that come from a worksheet, a user, or a calculation, build or validate the value through date functions rather than assuming that a text string such as “03/04/2026” means the same thing on every computer. Functions that interpret date text can depend on regional settings.

Date arithmetic and elapsed time

Subtracting one Date from another gives a duration measured in days, including a fractional day. If a process starts at 9:00 a.m. and ends at 3:00 p.m. on the same day, the difference is 0.25 days, or six hours. Store a duration in a Double when you want to do further arithmetic or express it as hours, minutes, or seconds.

Sub MeasureElapsedHours()
    Dim startedAt As Date
    Dim finishedAt As Date
    Dim elapsedDays As Double
    Dim elapsedHours As Double

    ' These timestamps are on the same day.
    startedAt = DateSerial(2026, 11, 2) + TimeSerial(9, 0, 0)
    finishedAt = DateSerial(2026, 11, 2) + TimeSerial(15, 0, 0)

    elapsedDays = finishedAt - startedAt
    elapsedHours = elapsedDays * 24
    Debug.Print elapsedHours  ' Prints 6
End Sub

For a duration spanning midnight, use complete timestamps: subtracting 11:00 p.m. on one date from 1:00 a.m. on the next produces two hours. If you store only the time of day, the date needed to distinguish an overnight interval is missing. For a running timer, the VBA Timer function returns seconds since midnight as a Single; account for its reset at midnight rather than treating it as an unrestricted timestamp.

DateDiff answers a different question: it counts interval boundaries, such as days or months, rather than returning a fractional elapsed duration. Two timestamps separated by a few minutes across midnight can have a day-boundary count of one. Use subtraction when you need precise elapsed time in days, and use DateDiff when the number of crossed calendar intervals is what matters. DateAdd is useful for moving a date by a stated number of intervals, such as adding days or months.

Choosing Date, Double, or Variant

Pick the type based on what the value means, not simply on how it will look when printed. These distinctions prevent many conversion and calculation surprises:

Date: a real calendar date, a time of day, or a combined timestamp, such as an invoice date or appointment time.
Double: a calculated duration in days, hours, or another numeric unit, especially when adding many durations together.
Long: a whole-number count, such as the number of days reported by a count or an iteration variable. It is not a timestamp type.
Variant: a flexible container when a value may be empty, an error, text, or a date. Check and convert its contents before doing date arithmetic.

A Currency value is intended for fixed-point monetary amounts, not dates or elapsed time. A String that looks like a date is still text: its meaning may change with regional conventions, and text does not behave like a date in comparisons or arithmetic. Convert valid input into Date values as early as possible, then keep it typed.

Working with dates in worksheet cells

Excel worksheet dates are stored as serial numbers, and number formatting controls whether a cell displays that number as a date, a time, or a timestamp. Formatting does not turn arbitrary text into a real date. A cell that visibly shows a date may therefore contain a number, while a cell containing date-looking text may remain text.

In VBA, Range.Value returns a Variant and can represent a cell as a Date when the cell is formatted as a date. Range.Value2 does not use VBA’s Date or Currency subtypes; date-formatted cells are generally exposed as numeric values. This matters when comparing values, transferring data, or inspecting a cell in the debugger. The workbook’s date system also affects worksheet serial numbers, so do not assume a raw cell serial is interchangeable with a VBA Date number in every calculation.

For routine automation, read a worksheet value into a Variant, check that it is a usable date, and then assign it to a Date variable. For example, a macro that expects a date in cell A1 should handle an empty cell or invalid text rather than blindly converting it. When writing a VBA Date to a worksheet, also apply an appropriate number format if readers need to see a date and time; the cell’s display format is separate from the value.

Common conversion and accuracy problems

The most frequent error is treating dates as strings. A value such as “04/05/2026” can be interpreted differently in regions that use month-first and day-first formats. IsDate can help test whether a value is recognized as a date, but recognition of text follows the system’s conventions; it does not establish that the author’s intended date was understood. For user input, prefer unambiguous separate fields or a clearly specified format and validate the result.

Another common misconception is that a Date variable carries a time zone. It does not. Now reflects the machine’s local system clock, and Date values do not remember whether they were entered as local time, UTC, or another zone. If a workbook coordinates events across time zones, store or label the zone separately and define a consistent conversion policy. Daylight-saving changes can also affect real-world elapsed time even when simple local clock subtraction appears straightforward.

Finally, do not confuse a display such as “2:30 PM” with a stored time-only value. Excel and VBA can represent a time as a fraction of a day, but dates, formatting, and the workbook date system affect how that number is interpreted. For reliable reports, preserve full timestamps where the date matters, and format the worksheet cell explicitly for the audience.

Frequently Asked Questions

What is the VBA data type for date and time?

Use Date. It can hold a date, a time, or both in one variable. VBA has no separate built-in Time variable type.

Should I use Date or Double for elapsed time?

Use Date for start and finish timestamps, then subtract them. Store the resulting duration in a Double when you need to accumulate it or convert it into hours, minutes, or seconds.

How do I get the current date and time in VBA?

Assign Now to a Date variable for the current system date and time. Use Date when you need the current date, or Time when you need the current time of day.

Why does a worksheet date appear as a number in VBA?

Excel stores dates as numeric serial values. Reading a cell with Value2 can expose that number, while Value may return a Date subtype for a date-formatted cell. The cell’s number format affects display, not the underlying fact that the value is numeric.

Does a VBA Date value include a time zone?

No. A Date stores a date and time but no time-zone identifier or UTC offset. Keep zone information separately if the workbook must compare timestamps from different locations.

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.