Excel VBA: Insert the Current Date and Time in a Cell

To insert a fixed date-and-time timestamp with Excel VBA, assign the VBA function Now to a cell, then apply a number format. For example, this macro writes the current date and time into the active cell: ActiveCell.Value = Now. The result is a stored value, so it will not change when the workbook recalculates.

What VBA date and time values mean

VBA provides three closely related functions for working with the computer’s local system clock. Date returns the current date, Time returns the current time, and Now returns both together. These are values captured when the code runs; they are not formulas that keep updating.

Excel stores dates and times as serial numbers. The whole-number portion represents the date, while the decimal portion represents the time of day. For instance, a value can contain both parts even if a cell’s display format shows only the date. Applying a different number format changes what you see, not the underlying timestamp.

VBA reads the computer’s local clock. If that clock or its time zone is wrong, the timestamp can be wrong too. A workbook macro does not automatically provide a trusted, centrally synchronized time or convert the value to another time zone.

Choose Date, Time, or Now

Use the function that matches the information you want to record. The choice affects the stored value, while the cell’s number format controls how much of that value is visible.

Date records today’s date without a meaningful time-of-day component.
Time records the current time without a date. It is useful for a time-of-day entry, but it cannot identify which day the time belongs to.
Now records the current date and time together. This is usually the right choice for a timestamp such as a submission or processing time.

For example, use Date when a daily checklist needs the date it was completed. Use Now when a record should show both the day and the moment it was entered. Do not use a date-only format to conclude that a timestamp has no time component: the time may simply be hidden.

Insert a timestamp in the active cell

Before running a macro, save a copy of the workbook. Excel macros cannot be undone with Ctrl+Z, so a mistaken write may require restoring the saved copy. The following example targets the currently active cell and uses a clear, 24-hour display format.

Sub InsertCurrentDateTime()
    Dim target As Range

    ' Use the currently active worksheet cell.
    Set target = ActiveCell
    target.Value = Now
    target.NumberFormat = "yyyy-mm-dd hh:mm:ss"
End Sub

For example, if the macro runs at 3:07:09 PM on November 2, 2026, the cell displays 2026-11-02 15:07:09. The actual result depends on the computer’s clock and the moment the macro runs. The assignment replaces the cell’s existing contents, so select a blank cell or a cell you intend to overwrite.

The NumberFormat line controls the appearance. In this format, yyyy shows a four-digit year, mm a two-digit month, dd a two-digit day, and hh:mm:ss the time to seconds. You can remove :ss if seconds are not useful, or use a 12-hour display such as m/d/yyyy h:mm AM/PM. Formatting does not change the stored value’s precision.

Put the macro in a workbook and run it

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the procedure into the new module, and return to Excel. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain the VBA code. Saving as .xlsx does not preserve VBA macros.

Select the destination cell, then run the macro with Developer > Macros, choose InsertCurrentDateTime, and select Run. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer. You can also run a macro from the Macros dialog with Alt+F8 on Windows. On a Mac, use Tools > Macro > Macros; keyboard shortcuts can vary with the keyboard and function-key settings.

Excel for the web can open and edit a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Open the file in the Excel desktop application to use this procedure. On a Mac desktop version of Excel, the VBA editor and macro features are available, though ribbon and keyboard details can differ from Windows.

Write to a chosen cell or a selection

To write to a fixed location instead of whichever cell happens to be active, replace ActiveCell with a worksheet reference. For example, ThisWorkbook.Worksheets("Log").Range("A2") identifies cell A2 on the sheet named Log in the workbook containing the macro. If the sheet name changes or does not exist, the reference will fail, so use the actual tab name in the workbook.

A macro can also write to every cell in the current selection. Assigning a single value to a multi-cell range fills the selected cells with that same value. This can be useful when marking a group of records with one batch-processing time, but it is easy to overwrite data accidentally. Check the selection before running a macro, especially if the code targets Selection rather than a known cell.

When a timestamp is meant to document an event, capture it once and store it as a value. Avoid replacing a single-cell assignment with a formula unless an updating timestamp is specifically desired. If a macro should timestamp a particular data-entry action, design it to target the relevant row or cell rather than relying on the user’s current selection.

Format and understand the stored timestamp

Excel may apply a default number format that displays only part of a date-time value. Set NumberFormat explicitly in the macro when consistent display matters. Common choices include m/d/yyyy h:mm AM/PM for a compact 12-hour display and yyyy-mm-dd hh:mm for a sortable-looking 24-hour display. The format is a display choice; it does not turn a numeric date-time into plain text.

Keeping the value numeric has practical advantages. Excel can sort timestamps chronologically, calculate elapsed time, and use date or time formats. If you convert the timestamp to text with a VBA formatting function, it may look right but behave like text in later calculations or sorting. Store the date-time value and format the cell unless text is specifically required for an exported label.

Cell formatting can also be set manually through Home > Number Format, or by opening Format Cells with Ctrl+1 on Windows. On Mac, use Command+1 to open Format Cells. A custom format can be entered on the Number tab. A cell format may be overridden by a table style or later formatting, but that still does not alter the value written by VBA.

VBA timestamps compared with Excel shortcuts and NOW

For a one-off manual entry, VBA may be unnecessary. In Excel for Windows, Ctrl+; inserts the current date as a fixed value, and Ctrl+Shift+; inserts the current time. You can enter the date, type a space, and then insert the time to make a combined static entry. Mac shortcuts can depend on the keyboard layout; the Control-based equivalents are commonly available, but use Excel’s shortcut help if the keys behave differently.

A worksheet formula such as =NOW() is different from a VBA timestamp. It returns the current date and time and updates when Excel recalculates the workbook. That is useful for a live “current time” display, but not usually for recording when a specific event occurred. A value written by Now in a macro stays fixed until something changes or overwrites it.

Choose a shortcut for a quick manual entry, a VBA macro for repeatable or controlled writes, and =NOW() for a value that should update. These approaches are not interchangeable: the first two store fixed values, while the formula is recalculated.

Common problems and safe macro use

The macro is missing after saving: Save the workbook as .xlsm, not .xlsx, if you need to keep VBA code.
The macro will not run: Excel may have disabled macros because of security settings or the file’s source. Only enable macros in files you trust; do not weaken security settings just to run an unfamiliar workbook.
The time looks wrong: Check the computer’s date, time, and time zone, then confirm the cell’s number format. A hidden time portion or a 12-hour display can make a correct value appear unexpected.
The macro changed the wrong cell: A procedure using ActiveCell writes wherever the selection currently is. Select the intended cell first, or change the code to reference a specific worksheet and cell.
You cannot undo the write: VBA actions clear Excel’s normal undo history. Save a copy before running a macro and inspect the target range carefully.

VBA is suitable for ordinary workbook timestamps, but it is not a tamper-proof audit trail. Users who can edit the workbook may change the stored value, and the recorded time comes from the local computer. For regulated or security-sensitive records, use an appropriately controlled system rather than treating an editable spreadsheet macro as proof of when an event occurred.

Frequently Asked Questions

How do I insert today’s date only with VBA?
Assign Date to the target cell, such as Range("A1").Value = Date. Apply a date number format if the display is not what you want.

How do I insert only the current time?
Assign Time to the target cell. Format the cell as a time; remember that this value does not include the date on which the time was recorded.

Will a VBA timestamp update automatically?
No. A value assigned from Now is fixed at the time the macro runs. A worksheet formula such as =NOW() can update when Excel recalculates.

Can I add the timestamp automatically when a row is edited?
Yes. A worksheet event procedure can respond to a change and write Now into a timestamp column. It must be placed in that worksheet’s code module, not a standard module, and should be designed carefully to avoid repeated events or overwriting an existing timestamp.

Does this work in Excel for the web?
Excel for the web does not run VBA macros. Open the workbook in the desktop version of Excel to run VBA, or use a manual entry method when a macro is not available.

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.