In Excel VBA, Date is a built-in data type for storing a calendar date, a time, or both. Declare a variable with Dim meetingTime As Date; use functions such as DateSerial to construct dates reliably, and remember that Excel cells store date values as numbers rather than as a special date-only kind of cell.
What the VBA Date type represents
A VBA Date value represents a point on a calendar and clock. It can hold just a date, just a time, or a date and time together. For example, a value can represent 3 November 2026 at 2:30 p.m. If a value contains no time component, its time is midnight; if it contains only a time, its date component is associated with VBA’s date-number system.
The type is useful when code needs to compare dates, calculate deadlines, record timestamps, or pass date values to Excel. It is distinct from the String type, which stores characters that may look like a date, and from Long or Double, which store numbers without expressing that they are dates. A correctly typed date lets VBA apply date-aware functions and conversions.
VBA’s Date range runs from 1 January 0100 through 31 December 9999. The value includes time information, with a day running from midnight to just before the next midnight. A Date variable is eight bytes in VBA and is represented using a floating-point date value. That representation is convenient for date arithmetic, but it also means it is not an ideal format for exact scientific timing or precision measurements.
How dates are stored and counted
VBA dates use the OLE Automation date convention, in which a numeric value corresponds to a date and its fractional part corresponds to the time of day. The reference point for the numbering system is 30 December 1899. For example, the fraction 0.5 represents noon because it is half of a day. This explains why numbers and dates can be converted between one another, but it does not mean that an ordinary number variable carries date meaning by itself.
Excel worksheets also store dates as serial numbers, with the cell’s number format controlling how the number appears. A cell might contain a numeric value and display it as 3-Nov-2026, 11/3/2026, or a date and time. Changing the format changes the display, not the underlying value. The workbook’s date system affects how worksheet serials map to calendar dates: Excel supports the 1900 and 1904 date systems, which differ by 1,462 days.
There is an important historical exception in Excel’s 1900 date system: Excel treats 1900 as though it had a 29 February, although it did not. That compatibility behavior means Excel serial number 60 represents a date VBA cannot represent as a real calendar date. Most current date work is far from this edge case, but it matters when importing very old serial-date data or writing code that converts raw serial numbers manually. In ordinary workbook automation, let Excel handle the conversion rather than assuming a serial number has a universal meaning.
Declaring and creating Date values
Declare a variable explicitly when it is meant to hold a date. Use Date to get the current date, Now to get the current date and time, and Time to get the current time. These built-in values are useful for tasks such as dating a report or recording when a macro runs.
For a known calendar date, DateSerial is often clearer than typing a date literal or building a date string. Its arguments are year, month, and day, in that order. This avoids ambiguity between regional formats where, for instance, 3/11/2026 might mean 3 November or 11 March. TimeSerial similarly creates a time from hour, minute, and second arguments.
Sub WriteReportDate()
Dim reportDate As Date
Dim target As Range
' Use the current date, without the current time.
reportDate = Date
Set target = ThisWorkbook.Worksheets("Sheet1").Range("B2")
' Write the date and choose how it appears in the cell.
target.Value = reportDate
target.NumberFormat = "yyyy-mm-dd"
End Sub
This example assumes the workbook has a worksheet named Sheet1. Before running a macro that changes a workbook, save a copy: macro actions cannot be undone with Ctrl+Z. The format string affects presentation only; the cell still contains a date value that Excel can use in calculations.
Date literals enclosed in number signs can also appear in VBA code, but literal interpretation can be less readable across regional settings. For code that must clearly mean a particular year, month, and day, prefer DateSerial(2026, 11, 3). Note that DateSerial can normalize out-of-range parts: an invalid-looking day such as 32 may roll into the following month. Validate input if the code must reject impossible dates instead of adjusting them.
Date arithmetic and comparison
VBA supports date comparisons and arithmetic. Comparing two dates can establish which comes first, and subtracting one date from another can produce the elapsed number of days. Use DateAdd when the intention is to add a calendar interval, such as a month or a day, and DateDiff when the intention is to count boundaries for a specified interval. Their interval arguments include values such as "d" for days and "m" for months.
These operations answer different questions. Adding 30 days is not the same as adding one calendar month: a month can contain 28, 29, 30, or 31 days. Similarly, DateDiff with a month interval counts month boundaries, not a rounded number of full months. Choose the operation that matches the business rule rather than treating every date calculation as a simple number of days.
A date also includes any time component it carries. If two values fall on the same calendar day but one is at 9 a.m. and the other at 5 p.m., they are not equal as full Date values. If a comparison should ignore time, compare date-only values or deliberately remove the time component. In VBA, DateValue extracts the date portion of a value, while TimeValue extracts the time portion.
Reading and writing dates in worksheet cells
Excel’s Range.Value property returns a Variant. For a cell formatted as a date, Excel can return a Date value through Value. The Value2 property does not use VBA’s Date or Currency subtypes; date-like values are returned as numbers instead. That distinction is useful when reading large ranges or handling raw serial values, but it means code using Value2 must keep track of what those numbers represent.
When code is meant to work with actual calendar dates, use a Date variable and assign it to the range’s Value property. Set the cell’s NumberFormat separately if a consistent display is needed. This keeps the stored value useful for sorting and arithmetic while making its appearance predictable. A displayed date can still be text, however: data imported from a CSV or copied from another system may contain strings that resemble dates but are not recognized as date values.
Check untrusted input before converting it. IsDate can test whether an expression VBA recognizes as a date, and CDate can convert a recognizable value to Date. String interpretation can depend on regional settings, so a string like "04/05/2026" should not be treated as unambiguous. For imported data, establish the expected format and parse its year, month, and day deliberately rather than relying on the computer’s locale.
For example, reading a cell that contains a true Excel date into a Date variable is different from reading a cell whose visible contents are text. Applying a date number format to text does not convert the text into a date. If a worksheet column mixes dates, blanks, errors, and text, check the value type and content before converting or performing date arithmetic.
Common problems and misconceptions
- A date-looking cell is not necessarily a date. Formatting can make a numeric serial look like a date, but text that merely resembles a date remains text. Confirm the underlying value before relying on date calculations.
- Changing the format does not change the date value. A cell displayed as
11/3/2026may show a different layout after changing its number format, while still representing the same day. - A Date variable is not automatically “empty.” An uninitialized Date variable has the numeric value zero, which corresponds to the OLE Automation reference date, not to a missing-value marker. Track whether a date has been supplied with a separate Boolean flag, or use a Variant when the procedure genuinely needs an Empty or Null state.
- Regional date order can cause silent errors. A value parsed as month/day/year on one computer may be interpreted differently where day/month/year is customary. Prefer numeric construction with
DateSerialwhen the components are known. - Time values can affect equality and sorting. Two dates that display only the same day may differ because one contains a hidden time. Use a format that reveals time during troubleshooting, or compare date-only portions when that is the intended rule.
- Hard-coded serial offsets are risky. Manually adding or subtracting days to convert Excel serials can fail across workbooks using different date systems and near Excel’s 1900 compatibility exception. Use Excel’s range values and account explicitly for the workbook date system if raw serials must be exchanged.
When to use Date, Variant, or a number
Use Date when a variable should always represent a valid date or time and the procedure can require a value. It makes the code’s purpose clear and provides convenient conversions for date-aware functions. It is usually the best choice for a deadline, appointment, report date, or timestamp being processed by VBA.
Use Variant when the value may legitimately be a date, an empty cell, Null, an error, or another type that must be inspected before use. A Variant is more flexible but requires more care: test its contents before conversion or calculation. Use Double when the value is intentionally a numeric serial or a calculation where raw numeric representation matters, not merely because Excel happens to store dates as numbers.
For elapsed time measurements that need reliable precision, a Date variable is generally the wrong tool. It represents calendar date-and-time values, rather than functioning as a dedicated high-resolution timer. Choose a timing method designed for elapsed-time measurement when fractions of a second or performance comparisons matter.
Frequently Asked Questions
Is VBA Date the same as Excel’s date serial?
They are related but not interchangeable in every context. Both represent dates numerically, but worksheet serial interpretation depends on the workbook’s 1900 or 1904 date system, while VBA uses OLE Automation dates. Use Excel’s value properties for normal cell transfers instead of assuming a raw serial has the same calendar meaning everywhere.
How do I declare a date variable in Excel VBA?
Use a declaration such as Dim dueDate As Date. Assign it a date from a reliable source, such as Date, Now, a validated worksheet value, or DateSerial(year, month, day).
How do I remove the time from a VBA Date?
Use DateValue to obtain the date portion of a recognized date-and-time value. If comparing worksheet values, confirm that the value is actually date data and not text that only looks like a date.
Why does a VBA date display differently on another computer?
The stored value and its display are separate. Regional settings and the cell’s number format can change how a date is shown or how an ambiguous text date is interpreted. Set an explicit number format for consistent worksheet display, and avoid ambiguous date strings in code.
Can VBA Date store a time without a date?
A Date value can represent a time of day, but the type always uses a complete underlying date-and-time number. Use TimeValue when extracting the time portion, and decide how a time-only value should be represented when writing it to a worksheet.
Related
- Excel Split Cell Based on Character: Methods That Work
- Excel Split Cell at a Specific Character?
- Excel Slicers for Multiple PivotTables: How to Connect
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Clear Excel VBA: Contents, Formats, and CellsTo clear cells with Excel VBA, use Range("B2:D20").ClearContents to remove values and formulas while keeping formatting, or Range("B2:D20").Clear…
- How to Copy and Paste Using VBA in Excel: Macro GuideTo copy a range in Excel with VBA, use Range.Copy Destination:=...; for values only, assign one range’s .Value…
- Best Excel Macro Settings for Every Use CaseFor most people using Excel for Microsoft 365 on Windows, the best choice is Disable VBA macros with…
- Excel VBA: Copy and Paste Selected Cells with a MacroTo copy selected cells with VBA, call the range’s Copy method and specify a destination cell, as in…
- VBA Find First Empty Cell in a ColumnTo find the first truly empty cell in column A with VBA, scan from the top and test…
- VBA Dictionary Remove Key: Safe Removal and Common ErrorsTo remove one entry from a VBA Dictionary, call its Remove method with the key: dict.Remove key. If…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.