Excel VBA Date Value: Serials, Types, and Examples

VBA & macros · Updated November 21, 2026

In Excel VBA, a Date value is a date-and-time value that VBA stores as a number; use the Date data type for variables, DateSerial to build a date from numeric parts, and Range.Value or Range.Value2 to work with worksheet cells. The phrase “date value” can also mean the number Excel uses behind a displayed date, so it helps to distinguish VBA dates, Excel serial numbers, and formatted cell text.

What a VBA Date value means

VBA’s Date data type represents a calendar date and a time, even when the time is midnight and the worksheet only displays a date. A variable declared As Date can therefore hold values such as November 3, 2026 at 2:30 p.m. The value is not merely a string like "11/3/2026": it can be compared, incremented, and used in date calculations.

VBA dates are stored in a floating-point format compatible with OLE Automation dates. The whole-number portion represents days and the fractional portion represents a time within a day. For example, half of a day is 0.5, or 12 hours. This numeric representation is useful for calculations, but it does not mean that a date should be treated as an ordinary number when displaying or exchanging it. Formatting and the workbook’s date system affect how a number is interpreted in Excel.

VBA’s supported Date range is January 1, 100 through December 31, 9999. That is broader than the date range many business spreadsheets need, but it is not unlimited. A value outside the range cannot be represented as a VBA Date.

VBA Date versus an Excel serial number

Excel stores worksheet dates as serial numbers and applies a number format to show them as dates. In the default 1900 date system, January 1, 1900 is serial 1. VBA’s OLE Automation date numbering uses December 30, 1899 as zero, so the numeric value of a VBA Date is not always the same as the displayed worksheet serial. In particular, the 1900 date system includes the fictitious date February 29, 1900 for compatibility with older spreadsheet software. That historical quirk makes simple conversions around early 1900 especially error-prone.

For modern dates in the 1900 system, the worksheet serial and the numeric value of the corresponding VBA Date differ by one. Excel also supports the 1904 date system, whose serial numbering starts at a different point. A workbook using that system can show a different serial for the same calendar date than a workbook using the 1900 system. The setting belongs to the workbook and can affect date values when data is copied or transferred.

For ordinary VBA work, avoid manually adjusting a serial unless you have a specific conversion requirement. Let Excel convert between a VBA Date and a cell value, and use the cell’s date format to control what people see. A serial number is a storage representation, not a reliable human-readable date by itself.

Creating dates with DateSerial

DateSerial(year, month, day) constructs a Date from numeric year, month, and day arguments. It is often the clearest choice when the parts are already separate, such as values read from a form or stored in different cells. Unlike parsing a date string, it does not need to guess whether a slash-separated date means month/day/year or day/month/year.

Sub WriteSpecificDate()
    Dim target As Range
    Dim eventDate As Date

    ' Build November 3, 2026 from numeric parts.
    eventDate = DateSerial(2026, 11, 3)
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("A1")

    target.Value = eventDate
    target.NumberFormat = "mmm d, yyyy"
End Sub

The example writes a real date value to cell A1 and applies a display format such as Nov 3, 2026. The format changes the appearance, not the underlying date. A different format could show the same cell as 11/3/2026 or as a serial number. Before running any macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.

DateSerial normalizes out-of-range components. For example, a month value beyond 12 rolls into the following year, and day zero refers to the last day of the preceding month. This can be handy for date arithmetic, but it also means that DateSerial is not a strict input validator. If a user enters a month of 13 and your process must reject it, check the month range explicitly before constructing the date.

Parsing text with DateValue and CDate

DateValue(expression) converts a recognizable date expression to a Date and sets the time portion to midnight. It is appropriate when the source is text, but ambiguous strings depend on the computer’s regional date settings. For example, "03/04/2026" can mean March 4 or April 3. A workbook shared across regions should not rely on that text being interpreted identically.

CDate(expression) is a broader conversion function: it can convert a valid date or time expression to a Date, including a time component. It also interprets text according to regional settings, so it does not solve ambiguity. When the data comes from a database, CSV export, or user entry, define the expected format and validate the input rather than assuming every date-looking string is safe to parse.

For predictable dates assembled from numeric values, prefer DateSerial. For text arriving in a known, fixed format, parse its components deliberately and check that they form a valid calendar date. Avoid relying on a date literal or a hard-coded slash-separated string as a universal format across different locale settings.

Range.Value and Range.Value2

Range.Value and Range.Value2 both read or write a cell’s value, but they do not return date-formatted cells in exactly the same way. When a cell is formatted as a date, Value can return a Variant whose subtype is Date. Value2 does not use the Date or Currency subtypes; it returns the underlying value, so a date cell is generally read as a number. That makes Value2 useful for bulk data processing, but its numeric result should not be mistaken for a VBA Date without considering the workbook’s date system.

When the goal is to receive a VBA Date from a date-formatted cell, Value is often the more convenient property. Check that the cell actually contains a date before converting: an empty cell, an error value, or text that only looks like a date can behave differently. Here is a guarded example for a single cell:

Sub ReadDateFromCell()
    Dim source As Range
    Dim cellValue As Variant
    Dim actualDate As Date

    Set source = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    cellValue = source.Value

    ' Check before converting so invalid contents are not treated as a date.
    If Not IsError(cellValue) And IsDate(cellValue) Then
        actualDate = CDate(cellValue)
        MsgBox Format$(actualDate, "mmm d, yyyy h:mm AM/PM")
    Else
        MsgBox "Cell A1 does not contain a recognizable date."
    End If
End Sub

The check is useful but not a complete data-quality policy: IsDate accepts values that VBA can interpret as dates, and interpretation of text can be regional. For reliable imports, confirm the source format and validate the parts. Also remember that a cell’s number format is a display instruction; formatting ordinary text as a date does not turn that text into a valid date value.

Displaying and calculating with dates

A date’s stored value and its displayed appearance are separate. Set a cell’s NumberFormat when a macro should present a date consistently. For example, "yyyy-mm-dd" is an unambiguous display style for many data-entry and export contexts, while "mmm d, yyyy" is easier to scan in a report. The format does not change the calendar date or make an invalid value valid.

VBA can perform date arithmetic directly. Adding 1 to a Date advances it by one day; adding a fractional part represents a portion of a day. For calendar-aware changes such as moving a month forward, use DateAdd rather than assuming that every month has the same number of days. For example, adding one month to a date near the end of a month requires a defined rule for the resulting day, so test the behavior against the business requirement.

Use DateDiff when you need an interval counted in a specified unit, such as days or months. Its result depends on the interval you request; counting month boundaries is not the same as measuring a fixed number of days. For durations involving times, account for fractions of a day and decide how to handle daylight-saving transitions if the dates represent local clock times.

Common date-value problems

  • A date appears as a number: The cell may contain a valid serial but have General or numeric formatting. Apply an appropriate date format; do not replace the value with display text unless text is specifically required.
  • A cell looks like a date but is not recognized: It may contain text, hidden spaces, or an unsupported format. Check the underlying value and import rules instead of relying on appearance alone.
  • The date changes when shared: Regional parsing can swap month and day, or workbooks can use different date systems. Prefer explicit numeric components and verify the workbook’s date-system setting.
  • The macro adds the wrong number of days: Check whether the source contains a time fraction. A value displayed only as a date may still include hours, minutes, or seconds.
  • A value from Value2 looks one day off after conversion: A raw Excel serial is not automatically interchangeable with an OLE Automation date number. Use the date-aware cell value or a conversion that explicitly accounts for the workbook’s date system.

On Excel for Microsoft 365 on Windows, the workbook’s 1904 date option is under File > Options > Advanced, in the section labeled “When calculating this workbook.” Excel for Mac has the same underlying 1900/1904 distinction, although the location and wording of settings can differ by release. Excel for the web can display and edit dates, but it does not run VBA macros; use desktop Excel for VBA execution.

Choosing the right VBA approach

Choose based on the shape and purpose of the data, not just the name of the property. The following guide covers the most common situations:

  • You already have year, month, and day numbers: Use DateSerial, then validate the input ranges if invalid values must be rejected.
  • You have text in a known date format: Parse according to that format and validate it. Use DateValue when the text is unambiguous under the machine’s locale.
  • You need the current date and time: Use the VBA Now function; use Date when only the current date is needed.
  • You are transferring a date to or from a worksheet cell: Use Range.Value when a Date subtype is useful, and set NumberFormat for display. Use Value2 when working with raw values in bulk and you understand the serial-number context.
  • You need a label or export string: Format the date explicitly with Format$ or a cell number format. Keep a true Date value for calculations whenever possible.

Frequently Asked Questions

Is an Excel VBA date a number?
Internally, VBA Date values use a floating-point date-and-time representation, and worksheet dates are represented by serial numbers. In code, declare dates as As Date when you mean calendar dates; do not use an unexplained number as a substitute for a date.

What is the difference between DateValue and DateSerial?
DateValue converts a date expression, commonly text, into a Date and drops any time. DateSerial builds a date from numeric year, month, and day arguments, which is usually clearer when the components are already separate.

Why does Range.Value2 return a number for a date?
Value2 returns the underlying value without converting date-formatted cells to the VBA Date subtype. The number is the worksheet serial in that workbook’s date system, not necessarily a value that can be passed directly to CDate as though it were an OLE Automation date.

How do I put today’s date into a cell with VBA?
Assign VBA’s Date function to the cell’s Value property, then set the cell’s NumberFormat if you want a particular display. The Date function supplies today’s date without the current time; Now includes the time.

Does changing a cell’s date format change its value?
No. A number format changes how Excel displays the stored value, not the underlying date or time. Formatting text as a date also does not, by itself, convert that text into a date value.

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.