Excel VBA Date Variable: Types, Examples, and Tips

VBA & macros · Updated November 21, 2026

In Excel VBA, declare a date variable with Dim myDate As Date. A VBA Date variable can hold a calendar date, a time, or both; use DateSerial to create a date reliably and Now, Date, or Time to get the current system date and time.

What a VBA Date variable stores

The VBA Date data type is an eight-byte date-and-time value. Internally, it behaves like a floating-point number: the whole-number portion represents days, and the fractional portion represents the time within a day. For example, noon is one-half of a day, or 0.5. You normally do not need to calculate with that underlying number; VBA can work with dates using readable date functions and operators.

A VBA Date can represent dates from January 1, 100 through December 31, 9999. It does not store a display format. The same value can be displayed as November 3, 2026, 11/3/2026, or 2026-11-03 depending on how it is formatted when shown in a cell or message.

That distinction matters: the variable contains a date value, while formatting controls how a person sees it. Changing a cell’s number format does not change the date held by a VBA variable, and changing a variable’s value does not determine the final appearance of a worksheet cell.

Declare and assign a Date variable

Declare the variable with the As Date clause. To avoid ambiguity between regional date formats such as month/day/year and day/month/year, construct a date with DateSerial(year, month, day) rather than relying on a typed date string.

Before running a macro that changes a workbook, save a copy: macro changes cannot be undone with Ctrl+Z. The following example only displays a date in a message box.

Sub ShowDateVariable()
    ' Save a copy of the workbook before running macros that change it.
    Dim reportDate As Date

    reportDate = DateSerial(2026, 11, 3)
    MsgBox Format$(reportDate, "mmmm d, yyyy")
End Sub

The variable receives November 3, 2026, and Format$ returns a text representation for the message box. The date remains a Date value in reportDate; the formatted result is text. Keep that difference in mind if you later need to sort, compare, or calculate with the value.

In the Visual Basic Editor on Windows, press Alt+F11 to open or close the editor. On a Mac, Option+F11 is the common equivalent; some keyboards require Fn as well. You can also open it from the Developer tab by choosing Visual Basic. If Developer is not visible, enable the Developer tab in Excel’s ribbon settings.

Get today’s date, the current time, or both

VBA provides three useful built-in functions. Each reads the computer’s current system clock when the code runs:

  • Date returns today’s date, with the time at midnight.
  • Time returns the current time, with the date portion set to the base date.
  • Now returns the current date and time together.

Assign the result to a Date variable when you want to preserve it for later use. For example, a macro that records when it started can assign Now once and reuse that stored value. If it calls Now again later, the second result may differ because the clock has advanced. Similarly, use Date for a date-only deadline or report date when the time of day is irrelevant.

These functions use the system clock and regional settings of the computer running the macro; they are not a substitute for a trusted server clock in a process that requires authoritative timestamps. A VBA Date value also does not include a time-zone identifier. If a workbook is used across time zones, the value alone cannot tell you which zone the clock reading came from.

Build dates and perform date arithmetic

Use DateSerial when the year, month, and day are separate numbers or when you want code that is independent of how a user types a date. It also normalizes out-of-range months and days, which can be useful for calculations but may conceal an input error if you do not validate the inputs first.

For example, with startDate declared as Date, DateAdd("d", 7, startDate) returns a date seven days later. The interval code "d" means days. DateAdd also supports intervals such as months and years; adding months this way handles differing month lengths more safely than adding a fixed number of days.

You can subtract one Date value from another to get the number of days between them. Use DateDiff when you want to state the interval explicitly, such as days or months. Be aware that a month is not a fixed number of days. For business-day calculations, weekends and holidays need additional rules; ordinary date addition counts calendar days.

When a date comes from user input or an external source, check it before converting it. IsDate can tell you whether VBA recognizes a value as a date, while CDate converts a recognized value. However, text interpretation can depend on regional settings. For reliable input, ask for separate year, month, and day values or use a clearly specified format and validate it.

Put a VBA date in an Excel cell

A Date variable can be assigned to a worksheet cell, and the cell can then be given an explicit number format. For example, the macro below writes a date to A1. Save a copy of the workbook before running it because the macro changes a cell.

Sub WriteDateToCell()
    ' Save a copy of the workbook before running this macro.
    Dim reportDate As Date

    reportDate = DateSerial(2026, 11, 3)
    With ThisWorkbook.Worksheets("Sheet1").Range("A1")
        .Value = reportDate
        .NumberFormat = "yyyy-mm-dd"
    End With
End Sub

Here, the cell displays 2026-11-03, while the stored value remains usable as a date for sorting and calculations. Change Sheet1 to the actual worksheet name in the workbook. If that sheet does not exist, the macro raises an error.

Excel stores worksheet dates as serial numbers and displays them through number formats. The workbook’s date system affects those serials: Excel supports the 1900 and 1904 date systems, which differ by 1,462 days for corresponding dates. VBA Date values and worksheet serial numbers are related, but they should not be treated as interchangeable raw numbers in every situation. Assigning a Date to a cell through the Excel object model is generally clearer than manually adjusting a serial.

There is also a historical compatibility quirk in Excel’s 1900 date system: it treats 1900 as if it had a February 29, although that date did not exist. VBA’s calendar date functions do not make that fictional day a real date. This is one reason to use date values and Excel’s date-aware handling instead of building logic around unexplained serial numbers.

Date versus Variant, String, and worksheet values

Use As Date when a variable is meant to hold a date or time. A String holds text, such as "2026-11-03"; it may look like a date but is not automatically safe for date arithmetic. A Variant can hold different kinds of values, including dates, but its flexibility can make code harder to reason about and can defer type errors until runtime.

Excel’s Range.Value property returns a Variant, so code reading a cell may receive a date, a number, text, or an error depending on the cell’s contents and formatting. Range.Value2 avoids the Currency and Date subtypes and returns underlying values such as date serials as numbers. That can be useful for low-level data handling, but it means your code must know whether a number is intended to represent a date. Check cell contents and handle blanks or errors before converting values to Date.

Do not confuse a cell’s appearance with its data type. A cell formatted to look like a date can still contain text, and a cell formatted as General can hold a numeric date serial without displaying it as a familiar date. If a value comes from a worksheet, inspect or validate it rather than assuming its formatting proves it is a valid date.

Common errors and how to avoid them

  • Ambiguous date text: A string such as "03/11/2026" can mean March 11 or November 3 depending on regional settings. Prefer DateSerial(2026, 11, 3) for code-defined dates.
  • Invalid values: Assigning unrecognized text to a Date variable can cause a type mismatch. Validate external input with IsDate and handle invalid or blank values deliberately.
  • Unexpected time portions: Two values that display as the same date may differ if one contains a time. If you need date-only comparisons, remove or account for the time portion rather than relying on the displayed format.
  • Text that only looks like a date: Formatting does not convert text into a true date value. Convert and validate the source data before using date arithmetic.
  • Wrong workbook or sheet: Qualify ranges with the workbook and worksheet you intend to change. Unqualified Range references can act on whichever sheet is active.
  • Assuming a fixed month length: Months vary in length, so adding 30 days is not the same as adding one calendar month. Use date-aware functions and define what should happen at month-end.

Frequently Asked Questions

How do I declare a date variable in Excel VBA?
Use Dim eventDate As Date inside a procedure or at an appropriate module level. Assign a valid date value, for example with eventDate = DateSerial(2026, 11, 3).

What is the difference between Date and Now in VBA?
Date returns the current date with no meaningful time-of-day component. Now returns the current date and time from the computer’s system clock.

How do I set a VBA date to today?
Declare a Date variable and assign Date to it, such as Dim todayDate As Date followed by todayDate = Date. Use Now instead if you also need the current time.

Why does my VBA date display differently on another computer?
The stored date can be the same while its display changes because of cell formatting or regional settings. Apply an explicit worksheet number format for consistent display, and avoid ambiguous date strings when creating values.

Should I use Date or Double for a date in VBA?
Use Date when the value represents a calendar date or time; it makes the intent clear and gives VBA date-aware behavior. Use Double only when you specifically need numeric handling and have clearly defined how the number maps to a date.

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.