Excel VBA Date Object: Dates, Times, and Examples Explained

VBA & macros · Updated November 21, 2026

In Excel VBA, Date is a built-in data type for storing a calendar date and, optionally, a time; it is not an object with methods or properties. Declare a variable with Dim eventTime As Date, then use VBA date functions such as DateSerial and DateAdd to create or calculate dates reliably.

What the VBA Date Type Means

A variable declared As Date can hold a date, a time, or both. VBA represents dates internally as a floating-point number: the date is associated with the number’s day value, and the time is represented as a fraction of a day. For example, noon is half a day. This numeric representation makes date arithmetic possible, but it does not mean that a date should be treated as an ordinary number in every situation.

The VBA Date type occupies eight bytes and supports dates from January 1, 0100, through December 31, 9999. It can represent times from midnight through the end of the day. A variable can therefore contain just a date, just a time, or a combined date and time. The way it appears depends on how you format or display it; the underlying value and its display format are separate things.

There is an important terminology distinction: Date is a data type, while Date() is a VBA function that returns the computer’s current system date. Now() returns the current system date and time, and Time() returns the current time. A variable such as Dim Date As Date is a poor choice because using a built-in name for a variable makes code harder to read and can interfere with referring to the function.

Declare and Create Dates Safely

Use DateSerial(year, month, day) when you want to construct a date from numeric parts. It avoids relying on the order in which a person’s computer expects month and day text to appear. The following macro writes a known date to cell B2 and applies a clear display format:

Option Explicit

Sub WriteExampleDate()
    Dim ws As Worksheet
    Dim reportDate As Date

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    reportDate = DateSerial(2025, 3, 8)

    ws.Range("B2").Value = reportDate
    ws.Range("B2").NumberFormat = "yyyy-mm-dd"
End Sub

This example assumes the workbook contains a worksheet named Sheet1. The date in B2 is March 8, 2025; the number format makes it appear as 2025-03-08. Change the worksheet name or target cell to suit the workbook. Before running a macro, save a copy of the workbook: changes made by VBA macros cannot be undone with Ctrl+Z. Excel for the web does not run VBA macros; use desktop Excel. Excel for Mac supports VBA, although some Windows-specific objects and integrations are unavailable or behave differently.

For dates that should be relative to the current day, assign Date to a Date variable. For a timestamp, use Now. These values come from the computer’s system clock, so they reflect its current date, time, and local settings—not a timestamp guaranteed to be synchronized across computers.

How VBA Date Values Work with Excel Cells

Excel stores worksheet dates as serial numbers and displays them using a number format. When VBA transfers a date between a variable and a cell, Excel can show the value as a date if the cell has a suitable format. Assigning a Date value does not always give a cell the exact appearance you want, so set the cell’s NumberFormat when the display matters. For instance, yyyy-mm-dd is often easier to interpret consistently than a short date such as 3/8/25.

The Range.Value property returns a Variant and can return a Date subtype when the cell is formatted as a date. Range.Value2 does not use the Date or Currency subtypes; date values are returned as numbers instead. This matters when code inspects cell contents or moves data between ranges. Use Value when a VBA Date subtype is useful, and consider Value2 when working directly with the underlying worksheet value. In either case, apply an appropriate number format if people need to read the cell as a date.

Do not assume a VBA date’s underlying number is interchangeable with every Excel date serial. Excel workbooks can use the 1900 or 1904 date system, and the two systems assign different serial numbers to the same calendar date. In the 1900 system, Excel also preserves the historical compatibility error that treats February 29, 1900 as a valid worksheet date; that calendar date did not exist and cannot be represented as an ordinary VBA Date. Let Excel and VBA convert date values through their date-aware properties rather than manually adding or subtracting serial numbers without accounting for the workbook’s date system.

Calculate and Compare Dates

VBA supports straightforward date arithmetic. Subtracting one date from another produces a number of days, including any fractional part caused by times. Adding 7 to a date adds seven days. That is useful for simple calculations, but named functions are often easier to understand and maintain in business code.

  • DateAdd("d", 7, startDate) returns a date seven days after startDate.
  • DateAdd("m", 1, startDate) adds a calendar month. Month-end results can differ from simply adding a fixed number of days.
  • DateDiff("d", startDate, endDate) counts day boundaries between the two values; it is not always the same as counting complete 24-hour periods when times are included.
  • Subtracting two Date values gives elapsed days. Multiply the difference by 24 for elapsed hours, or by 1440 for elapsed minutes.

For example, if a start value is March 8 at 9:00 a.m. and an end value is March 9 at 9:00 a.m., the elapsed difference is one day. If the end value is March 9 at 8:00 a.m., the elapsed difference is 23 hours, even though the dates fall on different calendar days. Pick a calculation that matches the question: elapsed time, calendar days crossed, or a future calendar date are not always equivalent measures.

VBA can compare Date values with operators such as <, >, and =. When both values include times, the time affects the comparison. Two values on the same calendar day are not equal if one is 9:00 a.m. and the other is 3:00 p.m. If the task is to compare only calendar dates, remove or ignore the time portion deliberately rather than assuming it is absent.

Parsing, Formatting, and Regional Settings

DateSerial is a good choice for fixed numeric components, but imported or typed text needs careful handling. CDate converts an expression that VBA recognizes as a date, and DateValue extracts a date from a recognizable date expression. What text is recognized can depend on the computer’s regional settings. A text value such as 04/05/2025 could mean April 5 or May 4, depending on the date convention. If data comes from a file or another system, confirm its format and parse its components explicitly rather than assuming a text date is unambiguous.

Use IsDate to check whether an expression can be recognized as a date, but do not treat it as a complete data-validation strategy. It does not establish that a date string uses the intended month-day order or meets a business rule such as “must be in the current financial year.” Validate both the input format and the allowed range when correctness matters.

Formatting is different from conversion. Format and Format$ return text formatted to a pattern; they do not turn a date into a different underlying date. Likewise, assigning a format to an Excel cell changes its display, not the stored value. Keep values as Date data while performing calculations, and convert them to text only when a text representation is actually needed, such as preparing a label or report heading.

Choosing Date, Variant, or Worksheet Values

For a variable that should always contain a date and time, declare it As Date. This makes the intent apparent and helps prevent unrelated kinds of values from being stored in that variable. Use a Variant when the value may genuinely have different types—for example, a value read from a worksheet that could be a date, text, an error, or an empty cell. Check a Variant’s type or validity before using it as a date.

A worksheet cell is not itself a VBA Date variable. It holds a worksheet value that VBA reads through a Range property, and the returned type can depend on the property used and the cell’s contents or formatting. This distinction is especially useful when debugging: a cell that looks like a date may contain text, while a cell displaying a number may contain a date serial with a general number format. Inspect the value and format separately rather than relying only on what the grid displays.

Use DateSerial for constructing dates from year, month, and day numbers; DateAdd for calendar-based increments; and ordinary subtraction for elapsed durations. These choices make intent more visible than unexplained numeric offsets. For example, adding 30 days is not the same as adding one month, because months have different lengths.

Common Date Problems and Their Fixes

  • A date appears as a number: The cell may have General or Number formatting. Apply a date number format; the value may already be a valid date serial.
  • A date looks right but calculations fail: The cell may contain text rather than a numeric date value. Check how it was imported and convert it only after confirming the text’s regional format.
  • Dates switch month and day: A string was interpreted using different regional settings. Use numeric components with DateSerial or parse a documented input format explicitly.
  • A one-month calculation is off: Adding a fixed number of days does not mean adding a calendar month. Use DateAdd("m", 1, someDate) when the requirement is calendar-month based.
  • Two dates that look identical do not compare equal: One or both may include a hidden time portion. Inspect or intentionally remove the time before comparing calendar dates.
  • A date appears shifted after copying a workbook: The source and destination workbooks may use different date systems. Check the workbook’s date-system setting and use date-aware transfers instead of assuming serial numbers match.

DateSerial also normalizes some out-of-range components. For example, a month value beyond 12 can roll into the following year. This behavior can be useful for calculations, but it can also hide invalid user input. If users are expected to enter a month from 1 to 12, validate that range before constructing the date. Use four-digit years to avoid ambiguity around two-digit-year interpretation.

Frequently Asked Questions

Is Date an object in Excel VBA?
No. Date is a built-in VBA data type. A worksheet cell or a VBA object such as a Range can hold or expose a date value, but the value itself is not a Date object.

How do I get today’s date in VBA?
Use the Date function, which returns the computer’s current system date without the time. Use Now if you need both the current date and time.

How do I create a date without regional-format problems?
Use DateSerial(year, month, day) with numeric arguments, such as DateSerial(2025, 3, 8). This avoids parsing an ambiguous string such as 03/08/2025.

Why does VBA show a date as a decimal number?
The date and time are represented numerically, and a cell or variable may be displaying that underlying value rather than a date format. Set the worksheet cell’s NumberFormat to a date pattern when presenting the value.

What is the difference between Date and DateValue?
Date is the data type and also the function that returns today’s date. DateValue is a conversion function that returns the date portion of a recognizable date expression; text interpretation can depend on regional settings.

Related

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.