In Excel VBA, declare a date variable with Dim variableName As Date, such as Dim dueDate As Date. The Date type stores a calendar date and can also store a time, making it appropriate for values you need to compare, calculate, or write to worksheet cells as dates.
What Dim As Date Means in VBA
Dim declares a variable, and As Date specifies its data type. The type tells VBA what kind of value the variable is intended to hold and how VBA should treat it in operations. A date variable is not just a text label that happens to look like a date: VBA can use it in date arithmetic and pass it to date-related functions.
For example, this declaration creates a variable named invoiceDate whose type is Date:
Dim invoiceDate As Date
VBA variable names are not case-sensitive, so invoiceDate and InvoiceDate refer to the same name. A descriptive name is useful when a procedure contains several dates, such as a start date, due date, and last-updated time. With Option Explicit at the top of a module, VBA also requires variables to be declared before use, which helps catch misspellings.
Declaring Date Variables in a Procedure
Declare a variable inside a Sub or Function before using it. In VBA, a declaration can appear after the procedure line and before the statements that use the variable. The following example builds a date without relying on text parsing:
Sub MakeDueDate()
Dim dueDate As Date
' Build November 3, 2026.
dueDate = DateSerial(2026, 11, 3)
MsgBox dueDate
End Sub
Before running any macro that changes a workbook, save a copy first: VBA macros cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert or select a standard module, and place the procedure there. On Mac, use Option+F11 (on some keyboards, Fn may also be needed) or open the editor from the Developer tab. Excel for the web can open and edit VBA-containing files, but it cannot create, run, or edit VBA macros; use the desktop app.
If you declare several variables of the same type, give each one its own type declaration. In VBA, this line declares only startDate as a Date; endDate is a Variant:
Dim startDate, endDate As Date
Prefer separate declarations to avoid that common trap:
Dim startDate As Date
Dim endDate As Date
How VBA Stores Dates and Times
A VBA Date value can represent dates from January 1, 100, through December 31, 9999. It can also include a time of day. Conceptually, the date is the whole-number portion of a numeric value and the time is represented by a fraction of a day: noon corresponds to half a day. You usually do not need to manipulate that numeric representation directly; let VBA’s date functions handle it.
A Date variable has no built-in time-zone or UTC-offset information. It cannot tell whether a value was recorded in one time zone or another. Functions such as Now and Date use the computer’s local clock: Now returns the current date and time, while Date returns the current date with the time set to midnight. If an application must preserve time-zone information, it needs to store that information separately.
VBA Date values and dates shown in Excel cells are related, but the appearance of a cell is controlled by its number format. A cell may contain a numeric date value while displaying a month name, a short date, or a date and time. Conversely, text that looks like a date is still text until Excel or VBA converts it.
Assigning Dates Reliably
For a known year, month, and day, DateSerial is often the clearest way to create a date. Its arguments are year, month, and day, in that order. This avoids the ambiguity that can arise when VBA has to interpret date text according to regional settings.
Sub CreateDateSafely()
Dim reportDate As Date
' Arguments are year, month, and day.
reportDate = DateSerial(2026, 11, 3)
Debug.Print Format$(reportDate, "yyyy-mm-dd")
End Sub
The example prints 2026-11-03 in the Immediate window. A date literal, written between number signs, is another option in VBA code, but literal text can be harder to read correctly across regional settings. For user input or imported text, conversion functions such as CDate may interpret a string differently depending on the computer’s locale. For predictable input, define the expected format and parse its components rather than assuming every machine reads the same date text the same way.
DateSerial also normalizes out-of-range month or day values. For instance, a month value of 13 rolls into the following year. That behavior can be useful for calculations, but it means that passing an invalid month or day does not necessarily raise an error. If the input must be a real date exactly as entered, validate the components before accepting the result.
Putting a Date in a Worksheet Cell
Assign the Date variable to a cell’s Value, then set the cell’s number format so the date is displayed the way you want. This macro places a fixed date in cell A1:
Sub WriteDateToCell()
Dim reportDate As Date
' Save a copy of the workbook before running this macro.
reportDate = DateSerial(2026, 11, 3)
With ThisWorkbook.Worksheets("Sheet1").Range("A1")
.Value = reportDate
.NumberFormat = "yyyy-mm-dd"
End With
End Sub
Change Sheet1 to the actual worksheet name in the workbook. The cell stores a date value; the number format controls its display. If you format a date cell as General, Excel may show a serial number instead of a familiar date, even though the underlying value has not become a different date.
When working with cells in VBA, Range.Value can return a Variant subtype such as Date when the cell is date-formatted. Range.Value2 avoids the Date and Currency subtypes and returns the underlying value as a number where appropriate. This distinction matters in code that checks types or processes many cell values. It does not mean that Value2 is a different way to format a worksheet date; formatting remains a separate cell property.
Formatting and Displaying Date Values
Formatting changes how a date is shown, not the date stored in the variable. Use Excel’s NumberFormat property for a worksheet cell, or VBA’s Format$ function when you specifically need a formatted string for a message or label.
yyyy-mm-dddisplays a four-digit year, month, and day, such as2026-11-03.dd-mmm-yyyydisplays a day, abbreviated month, and year, such as03-Nov-2026.m/d/yyyy h:mm AM/PMdisplays a date and a 12-hour time.
A formatted string is text, not a Date value. For example, Format$(reportDate, "dd-mmm-yyyy") returns a String. That is suitable for a message or a text heading, but keep the original Date variable if you still need to sort, compare, or calculate dates. Also note that format patterns can have locale-related display differences, especially for month names and separators.
Calculating and Comparing Dates
Date variables support common calculations. Use DateAdd to move forward or backward by a calendar interval, and DateDiff to count intervals between two dates. Direct subtraction can return a number of days, including a fractional day when the values contain times.
Sub CalculateFollowUp()
Dim sentDate As Date
Dim followUpDate As Date
sentDate = DateSerial(2026, 11, 3)
followUpDate = DateAdd("d", 14, sentDate)
Debug.Print Format$(followUpDate, "yyyy-mm-dd")
End Sub
This example sets the follow-up date to November 17, 2026. The first argument to DateAdd is the interval code; "d" means days. Other useful intervals include "m" for months and "yyyy" for years. Adding months is not always equivalent to adding a fixed number of days, so use the interval that matches the real rule you need.
Comparisons such as if startDate < endDate Then are useful for checking order, but make sure both values are actually dates and decide whether their time portions matter. Two values on the same calendar day can compare as unequal if one is midnight and the other is later in the day. If only the calendar date matters, normalize or compare the date parts deliberately rather than assuming the times are zero.
Common Date-Type Problems
Many date bugs are not caused by the Dim statement itself. They arise when a date crosses between VBA, worksheet cells, text, and regional settings. Check the value and its type at the point where it enters the procedure, not only where it is eventually displayed.
- A date appears as a number: The cell may be General-formatted. Set
NumberFormatto a date format; changing the display format does not require replacing the stored date. - A date is rejected or interpreted backward: Input such as
04/05/2026can mean April 5 or May 4 depending on regional conventions. Prefer a defined input format and parse it explicitly. - A declaration unexpectedly creates a Variant: In a comma-separated declaration, each variable needs its own
As Dateclause. - A comparison says same-day values differ: One value may contain a time component. Inspect the values with a date-and-time format or compare normalized date-only values.
- A string looks valid but fails later: A string that looks like a date is not automatically a Date variable. Validate and convert it before doing date arithmetic.
IsDate can check whether a value is recognizable as a date or time on the current system, but it does not prove that the value follows your application’s intended format or business rules. For example, locale-dependent text may pass the check while representing the wrong day-month order. Treat input validation as a separate step from declaring a variable.
Date Compared with String, Variant, and Double
Choose Date when the variable is meant to hold a calendar date or date and time. Compared with String, it supports date calculations and comparisons without treating the value as ordinary text. A String is appropriate when you need to preserve the exact characters supplied by a user, including a format that is not yet validated.
Variant can hold different kinds of values, including dates, but that flexibility makes code less explicit and can require additional type checks. Use it when a procedure genuinely needs to accept mixed types, such as a value read from a range that may contain blanks, text, or dates. A Double can hold a numeric serial-like value, but it communicates less clearly that the number represents a date and makes accidental arithmetic easier. For ordinary VBA date handling, a Date variable is the clearer choice.
One practical pattern is to receive uncertain input in a Variant, validate it, then assign the converted value to a Date variable. Keep a separate String if the original input must be preserved for an error report or audit trail. That separation makes it clearer which values are raw input and which have been accepted as dates.
Frequently Asked Questions
What is the correct VBA syntax for a date variable?
Use Dim myDate As Date inside a procedure, replacing myDate with a descriptive variable name. Put Option Explicit at the top of the module to require declared variables.
Can a VBA Date variable include a time?
Yes. A Date variable stores a date and an optional time together. A value assigned from Now includes the current time; Date gives the current date at midnight.
How do I assign a date without regional-format problems?
For known numeric components, use DateSerial(year, month, day). For example, DateSerial(2026, 11, 3) unambiguously constructs November 3, 2026, without parsing a regional date string.
Why does Excel show a number after I assign a Date variable?
Excel stores dates as numeric values and relies on the cell’s number format to display them as dates. Set the cell’s NumberFormat to a suitable pattern such as yyyy-mm-dd.
Is Date the same as a String that looks like a date?
No. A String contains characters; a Date is a typed date-and-time value. Convert and validate input text before using it in calculations, and keep it as a String only when its exact text representation is what you need.
Related
- Excel Text to Columns: Split Cell Text at Line Breaks
- Excel Text to Columns: Find It and Split Data Correctly
- Excel Text to Columns and Rows: How to Split Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: How to Edit a Worksheet with VBA MacrosTo edit a worksheet with Excel VBA, write a macro that identifies the worksheet and changes its cells,…
- Excel VBA Like Statement: Patterns and ExamplesIn Excel VBA, the Like operator compares a string with a pattern and returns whether the string matches…
- VBA Sort: How to Sort Excel Data Safely with a VBA MacroVBA does not have a standalone Sort function for worksheet data; in Excel macros, sorting is usually done…
- VBA Array Index: Bounds, Options, and ExamplesIn VBA, an array index is the number inside an array’s parentheses that identifies an element, such as…
- Microsoft Excel Cannot Run the Macro? Common FixesIf Microsoft Excel says it cannot run a macro, first check that you are using the desktop version…
- View Macros in Google Sheets: Find and Read the CodeTo view a macro in Google Sheets, open the spreadsheet in a web browser and choose Extensions >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.