Excel VBA Date: Get, Store, and Work With Dates

VBA & macros · Updated November 18, 2026

In Excel VBA, Date returns the current date from the computer’s system clock, without a time component; assign it to a variable declared as Date or write it to a worksheet cell. Use Now when you need both the current date and time. These names are easy to confuse with Excel’s worksheet function TODAY(), so it helps to know which kind of date you need before writing a macro.

What VBA’s Date Means

Date is both the name of a VBA data type and the name of a built-in function. In an expression, Date asks Windows for the current system date. In a variable declaration, As Date says that the variable will hold a date or a date-and-time value. For example, Dim runDate As Date declares storage, while runDate = Date retrieves today’s date.

The returned date comes from the computer’s clock and regional settings, not from the workbook or a cell. If that clock is wrong, VBA’s result will be wrong too. The value represents the date at the time the code runs; it does not update continuously while a macro is paused or while a workbook remains open.

Although VBA has a Date statement that can set the computer’s system date, that is different from reading the date with the Date function. Changing the system clock can affect other applications and may require permission. Ordinary Excel macros should read the date, not attempt to reset it.

Date, Now, and Excel’s TODAY Function

Choose the date source according to the job. VBA’s Date is appropriate for a macro that stamps a report with today’s date. Now includes the current time as well, which is useful for an audit timestamp. Excel’s worksheet function TODAY() returns the current date in a formula and recalculates according to Excel’s calculation settings.

  • Date: current date, with the time set to midnight.
  • Now: current date and current time.
  • TODAY(): a worksheet formula returning the current date when recalculated.
  • NOW(): a worksheet formula returning the current date and time when recalculated.

A VBA assignment such as Range("A1").Value = Date writes a value into the cell. It does not leave a live formula behind. By contrast, putting =TODAY() in a cell leaves a formula that can show a different date after recalculation. Use a fixed VBA value for a permanent “processed on” stamp, and a worksheet formula when the displayed date should keep changing.

Getting and Writing Today’s Date

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and place a procedure there. To show the date without changing the workbook, use Debug.Print; the result appears in the Immediate window. To put the date in a cell, assign it to that cell’s Value property.

Save a copy of the workbook before running any macro that changes cells: VBA actions cannot be undone with Ctrl+Z. The following example writes a fixed date value to A1 and applies a readable display format. It is not a worksheet formula.

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

    stamp = Date
    With ThisWorkbook.Worksheets("Sheet1").Range("A1")
        .Value = stamp
        .NumberFormat = "yyyy-mm-dd"
    End With
End Sub

Replace Sheet1 with the actual worksheet name, including spaces if present. The cell’s stored value remains a date; NumberFormat controls how it appears. A format such as dd-mmm-yyyy may display the same value as 02-Nov-2026, while yyyy-mm-dd displays it as 2026-11-02. The visible format does not change the underlying date.

On Excel for Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; the keyboard shortcut may require Fn along with Option+F11, depending on the keyboard. The VBA code itself is generally the same. Excel for the web can open workbooks containing macros, but it cannot create, edit, or run VBA macros; use the desktop application for those tasks.

Creating Specific Dates with DateSerial

Use DateSerial when a macro needs a known calendar date rather than today’s date. Its arguments are year, month, and day, in that order. This avoids ambiguous text such as “03/04/2026,” whose meaning can vary by regional date convention. With a variable declared as Date, the following procedure creates 4 March 2026 and displays it in the Immediate window:

Sub ShowFixedDate()
    ' Save a copy of the workbook before running a macro that changes it.
    Dim eventDate As Date

    eventDate = DateSerial(2026, 3, 4)
    Debug.Print Format$(eventDate, "dd-mmm-yyyy")
End Sub

DateSerial can normalize values outside the usual month and day ranges. For instance, a month value of 13 rolls into the following year. That behavior can be handy for calculating month boundaries, but it can also hide invalid input if a macro assumes every supplied month is between 1 and 12. Validate user-entered year, month, and day values when they must correspond exactly to a real date.

For a month-end calculation, a useful technique is to ask for day zero of the next month. With a year variable named yr and a month variable named mo, DateSerial(yr, mo + 1, 0) returns the final day of the requested month. The same rollover behavior makes this work for December when the next month becomes January of the following year.

Adding, Subtracting, and Comparing Dates

Date values support ordinary comparisons and subtraction. Subtracting an earlier date from a later one gives the number of days between them; comparing two dates with operators such as < or > checks chronological order. Be clear about whether an interval should include its starting day, ending day, or both, because inclusive counts often differ by one from simple subtraction.

For calendar-based changes, DateAdd is clearer than adding an unexplained number. Its interval argument can represent days, months, years, and other time units. This example calculates the date 30 days after today and prints the result:

Sub ShowFollowUpDate()
    ' Save a copy before running any macro that changes workbook content.
    Dim followUp As Date

    followUp = DateAdd("d", 30, Date)
    Debug.Print Format$(followUp, "dd-mmm-yyyy")
End Sub

Adding 30 days is not the same as adding one calendar month. For a date near the end of a month, adding a month can produce a different result from adding a fixed number of days. Decide whether the requirement means “after 30 days” or “on the same day of the next month,” and test month-end cases. DateDiff can count boundaries crossed for a chosen interval, but its result is not always interchangeable with subtracting two dates—for example, counting month boundaries is not the same as measuring a fixed number of days.

Reading Dates from Worksheet Cells

Excel cells can contain real date values, text that looks like a date, numbers, or blanks. A cell formatted to look like a date is not necessarily a valid date value. When a macro reads a cell, the result may be a Variant, so check what it contains before converting it. IsDate can test whether a value VBA recognizes as a date, while DateValue converts a recognized date string or value to a date without a time component.

Text conversion depends on the computer’s regional settings. A string such as 04/05/2026 can be interpreted differently where day/month order differs. Prefer actual Excel date values in input cells, or use clearly defined components and construct the date with DateSerial. If text must be accepted, document the expected format and validate the result rather than assuming a slash-separated date is unambiguous.

Also watch for empty cells and error values. A blank should not automatically be treated as a meaningful date, and a worksheet error such as #N/A needs handling before conversion. A robust macro checks for an empty value and for errors before using a cell’s contents in date calculations.

Formatting and Excel Date Serial Values

Excel typically stores dates as serial numbers and displays them according to a cell’s number format. In the commonly used 1900 date system, the serial number advances by one for each day. The 1904 date system, available for workbook compatibility, uses a different starting point. Excel workbooks can therefore have date-system differences, which matter when transferring raw serial numbers between workbooks.

VBA’s Date type and Excel’s worksheet serial representation are related through date values, but a reader should not infer a date merely from a cell’s appearance or copy a raw number between systems without considering the workbook settings. Assigning a VBA date value to a cell and letting Excel display it with an appropriate number format is usually safer than manually calculating or editing serial numbers.

Use NumberFormat when the cell should remain a date that users can sort, filter, and calculate with. Use VBA’s Format or Format$ function when you deliberately need a text label, such as a date in a message or filename. Formatted text is no longer a date value for spreadsheet calculations, so avoid converting dates to strings too early in a workflow.

Common Date Problems and Safe Macro Habits

  • A date appears as a number: the cell may contain a valid date with a General or numeric format. Apply a date format instead of changing the value.
  • A date is stored as text: formatting alone may not convert it. Check the cell’s value and convert only after confirming the text’s intended order and format.
  • Day and month are swapped: avoid ambiguous date strings and use DateSerial with separate year, month, and day values.
  • A timestamp is missing: use Now, not Date, if the time matters.
  • A date changes unexpectedly: distinguish a fixed value written by VBA from a recalculating worksheet formula such as TODAY().
  • A macro is blocked: run macros only from workbooks and sources you trust. Do not lower security settings merely to run an unknown file.

Before a macro edits many rows, test it on a duplicate workbook or a small sample. Confirm the worksheet name, target range, date interpretation, and desired display format. Macro security is also important: enable or run VBA only for trusted workbooks, since macros can make changes beyond the cell currently visible.

Frequently Asked Questions

How do I get today’s date in Excel VBA?
Use Date, for example Dim d As Date followed by d = Date. It reads the computer’s system date and returns no time component.

What is the difference between Date and Now in VBA?
Date returns today’s date at midnight. Now returns the current date and time. Choose Now for a timestamp and Date when only the calendar date is needed.

How do I put today’s date in a cell with VBA?
Assign it, such as Range("A1").Value = Date, then set the cell’s NumberFormat if you want a particular display. This writes a fixed value rather than a live formula.

How do I create a date from year, month, and day?
Use DateSerial(year, month, day). For example, DateSerial(2026, 3, 4) creates 4 March 2026 without relying on an ambiguous date string.

Can Excel for the web run VBA macros?
No. Excel for the web can work with a macro-enabled workbook, but VBA macros must be created, edited, and run in the Excel desktop application.

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.