Excel VBA Current Date: Date, Now, Formatting, and Examples

In Excel VBA, use Date to get the computer’s current date, without a time; use Now to get the current date and time. For example, assigning Date to a variable or worksheet cell gives you a date value that you can compare, calculate with, or format.

What Date and Now return

Date and Now are built-in VBA functions. Date returns a value of the VBA Date data type representing today at midnight. Now returns a date-and-time value representing the system clock at the moment VBA evaluates it. The value comes from the computer’s date and time settings; VBA does not independently determine the date from an internet time service.

For a time-only value, VBA also provides Time. These are values, not text strings. That distinction matters: a date value can be used in date arithmetic and comparisons, while text that merely looks like a date may need to be converted first.

Use Date for “today,” a date stamp, or a comparison against today’s date.
Use Now when the time of execution matters, such as recording when a macro ran.
Use Time when only the current time is relevant.

Store today in a VBA variable

Declare a variable as Date when you want to keep today’s date for use later in a procedure. The following macro assigns the current date once, then displays it in a message box. The displayed appearance depends on the computer’s regional date settings because the value is not given a custom format here.

Sub ShowToday()
    Dim todayDate As Date

    ' Capture the current date when this procedure runs.
    todayDate = Date
    MsgBox todayDate
End Sub

The assignment captures the date at that point in the procedure. If the macro runs across midnight, a variable already assigned from Date does not update itself. Assign Date again later if the procedure needs to check the date again. Similarly, Now is evaluated when the expression runs; it is not a continuously updating clock inside a variable.

To see the value without running a full macro, open the Visual Basic Editor and use its Immediate window. On Windows, Alt+F11 opens the editor in many Excel installations, and Ctrl+G opens the Immediate window. Enter ? Date and press Enter to print the result. On Mac, the editor can be opened from Excel’s Tools > Macro menu; the function-key shortcut can depend on the keyboard and macOS settings.

Write the current date into a worksheet

To place a static date in a cell, assign Date to the cell’s Value. This example writes today to A1 on a worksheet named Sheet1 and sets a clear year-month-day display. Change the sheet name or cell reference to fit the workbook. Before running a macro that changes a workbook, save a copy: VBA actions generally cannot be reversed with Ctrl+Z.

Sub WriteTodayToCell()
    Dim ws As Worksheet

    ' Point to the worksheet in this workbook.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Range("A1").Value = Date
    ws.Range("A1").NumberFormat = "yyyy-mm-dd"
End Sub

This writes a date value, not a formula. The cell will continue to show the date that was inserted even tomorrow, unless another macro changes it. The number format affects how Excel displays the value; it does not change the underlying date. You can instead use a format such as "dddd, mmmm d, yyyy" for a long display, or "m/d/yy" for a compact one. The cell may appear differently if a different number format is applied later.

If you want a timestamp, replace Date with Now and use a date-and-time number format. For the worksheet’s NumberFormat property, a format such as "yyyy-mm-dd hh:mm:ss" displays hours, minutes, and seconds. Excel uses number-format conventions for this property, so do not assume every format string used by VBA’s Format function has identical tokens.

Format a date as text

Sometimes the goal is a text label rather than a date value—for example, putting a readable date in a message or composing a filename. VBA’s Format function returns text in the requested pattern. In VBA format strings, m represents a month, while n represents minutes in a time format. Using nn for minutes avoids confusing minutes with month formatting.

Sub ShowFormattedDate()
    Dim dateLabel As String

    ' Format today's date as readable text.
    dateLabel = Format(Date, "dddd, mmmm d, yyyy")
    MsgBox dateLabel
End Sub

The result might look like “Monday, November 2, 2026,” depending on the actual date and the language and regional settings. The format controls the pattern, but names such as weekday and month can be localized by the system. If you need a portable date for sorting or filenames, a numeric pattern such as year-month-day is often less ambiguous than a month name.

Remember the difference between formatting a cell and converting a value to text. Setting NumberFormat keeps a date value available for calculations. Applying VBA’s Format function produces a string. Once a date has been turned into text, date comparisons and arithmetic may require conversion back to a date value.

Use the date in comparisons and calculations

VBA can compare date values and calculate relative dates. For instance, DateAdd can return a date a specified number of days from today. This macro displays the date seven days from the date it runs:

Sub ShowDateNextWeek()
    Dim nextWeek As Date

    ' Add seven calendar days to today's date.
    nextWeek = DateAdd("d", 7, Date)
    MsgBox Format(nextWeek, "yyyy-mm-dd")
End Sub

DateAdd accepts an interval code, a number of intervals, and a starting date. Common interval codes include "d" for days, "m" for months, and "yyyy" for years. Adding a month is not always the same as adding a fixed number of days: calendar months have different lengths, and VBA adjusts the result according to calendar-date rules.

A common comparison issue is a worksheet cell that contains both a date and a time. If the cell represents today at 9:00 a.m., comparing its full value directly with Date may not be equal, because Date represents today at midnight. To compare only the calendar date, check that the cell contains a date and use DateValue to remove the time portion:

Sub CheckWhetherCellIsToday()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Avoid converting an empty or non-date cell.
    If IsDate(ws.Range("A2").Value) Then
        If DateValue(ws.Range("A2").Value) = Date Then
            MsgBox "The date in A2 is today."
        End If
    End If
End Sub

Here, A2 is checked only if its value can be interpreted as a date. If the cell is blank, contains an error, or holds text that is not recognized as a date, the test will not report a match. For large data-cleaning jobs, it is also sensible to decide how the macro should handle invalid entries rather than silently treating them as dates.

Static date stamp or automatically updating date?

Choose between a macro-written value and a worksheet formula based on whether the date should stay fixed. A value inserted by VBA using Date is a snapshot: it records the date when the macro ran. This is useful for an entry date, a processing date, or a report-generation date that should not change on later recalculation.

A formula such as =TODAY() is different: it returns the current date when Excel recalculates the worksheet, so it can advance to a new day. The formula is not a permanent record of when a row was entered. =NOW() returns a current date and time and, like TODAY(), is a volatile worksheet function that can update when recalculation occurs. Use a VBA value for a fixed stamp; use a worksheet function when the display should track the current date or time.

There is a related misconception worth avoiding: setting a cell’s number format to a date does not make a text entry into a reliable date value, and it does not freeze a changing formula. Formatting changes presentation. The source—constant value, formula, or text—determines how the cell behaves.

Common problems and platform limits

The date looks wrong: Check the cell’s number format and the computer’s regional settings. The underlying value may be valid even if it is displayed in an unfamiliar order.
A comparison fails: The cell may include a time component, or it may contain text rather than a date value. Compare date-only values where appropriate and validate inputs.
The value changes tomorrow: Confirm whether the cell contains a formula such as =TODAY() rather than a fixed value written by a macro.
The macro does not run: VBA macros require the desktop version of Excel. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros.
The macro is blocked: Excel’s macro security settings and the workbook’s source can affect whether code runs. Only enable macros in files you trust; do not weaken security simply to run an unknown file.

Excel for Microsoft 365 on Windows supports VBA in desktop workbooks. Excel for Mac also supports VBA, although some Windows-specific automation, operating-system calls, and controls may not work the same way on Mac. The basic date functions shown here are standard VBA, but a larger macro may need platform-specific adjustments. Save macro-enabled work in a format such as .xlsm so the VBA project can be retained.

Frequently Asked Questions

What is the VBA function for the current date?
Use Date. It returns the current system date without a time component. Use Now if you need both the date and current time.

How do I insert today’s date in an Excel cell with VBA?
Assign Date to a cell’s Value, such as ws.Range("A1").Value = Date. Set the cell’s NumberFormat if you want a particular display, and remember that this creates a fixed value.

What is the difference between Date and Now in VBA?
Date represents today at midnight; Now represents the current date and time. Choose based on whether the time portion matters.

How do I show the date as yyyy-mm-dd in VBA?
For a worksheet cell, set its NumberFormat to "yyyy-mm-dd". To create a text string in VBA, use Format(Date, "yyyy-mm-dd").

Will a VBA date stamp update automatically?
No. A date assigned as a value stays fixed unless code changes it. A worksheet formula such as =TODAY() can update when Excel recalculates.

Can I run Excel VBA in a browser?
No. Excel for the web does not run, create, or edit VBA macros. Open the workbook in the desktop Excel app to use VBA.

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.