To add a fixed date-and-time stamp with Excel VBA, assign Now to a cell and apply a date-time number format. For an automatic stamp when a value is entered in a worksheet column, use a Worksheet_Change event; unlike =NOW(), a value written by VBA does not change whenever the workbook recalculates.
What an Excel date and time stamp means
A timestamp records a date and a time together, such as 2026-11-03 14:25:00. Excel stores dates and times as numbers: the whole-number portion represents the date, and the fractional portion represents the time within a day. A time of noon, for example, is one-half of a day. The cell’s number format controls how that underlying value appears.
In VBA, Now returns the current date and time according to the computer’s system clock. Date returns the current date without a time component, while Time returns the current time. Use Now when a record needs both parts. A timestamp made this way is a record of the computer’s local clock at the time the macro runs; it is not automatically a verified or universal time, and it does not provide a UTC time-zone conversion.
It is useful to distinguish a timestamp from a live clock. A fixed timestamp stays as the value VBA wrote until someone changes or removes it. A live clock is calculated or repeatedly updated, so its displayed value can change later.
Add a fixed timestamp to the selected cell
This small macro writes the current date and time into the active cell. It also sets a readable format that shows seconds. Put it in a standard VBA module, then run it with the cell you want stamped selected.
Sub InsertDateTimeStamp()
Dim stampCell As Range
' Use the currently selected cell.
Set stampCell = ActiveCell
stampCell.Value = Now
stampCell.NumberFormat = "yyyy-mm-dd hh:mm:ss"
End Sub
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the procedure, and close the editor. To run it, select a cell, press Alt+F8, choose InsertDateTimeStamp, and select Run. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.
On a Mac, open the Visual Basic Editor from the Developer tab or use the available Excel keyboard shortcut for your keyboard and version; Mac shortcuts can differ. VBA is supported in desktop Excel for Mac, but VBA macros do not run in Excel for the web. If the Developer tab is hidden, enable it in Excel’s ribbon settings. Save a macro-enabled workbook as .xlsm so the code can be retained.
Stamp a row automatically when data is entered
For a log sheet, a common pattern is to enter or edit a value in column B and write the timestamp in column A on the same row. The following event procedure handles individual edits and pasted ranges. Put it in the code module for the specific worksheet, not in a standard module.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changedCells As Range
Dim oneCell As Range
Dim eventsWereEnabled As Boolean
' Only respond to edits in column B.
Set changedCells = Application.Intersect(Target, Me.Columns("B"))
If changedCells Is Nothing Then Exit Sub
eventsWereEnabled = Application.EnableEvents
On Error GoTo CleanUp
Application.EnableEvents = False
For Each oneCell In changedCells.Cells
If Len(oneCell.Value2) > 0 Then
Me.Cells(oneCell.Row, "A").Value = Now
Me.Cells(oneCell.Row, "A").NumberFormat = "yyyy-mm-dd hh:mm:ss"
Else
' Clear the stamp when the corresponding entry is cleared.
Me.Cells(oneCell.Row, "A").ClearContents
End If
Next oneCell
CleanUp:
Application.EnableEvents = eventsWereEnabled
End Sub
To add it, right-click the relevant sheet tab and choose View Code, then paste the procedure into that sheet’s code window. In the example, column B is the edited field and column A is the timestamp. Change the column references if your layout differs. The code updates the timestamp on every edit to a nonblank cell in B; it does not preserve the first-entry time. If you want to record only the first entry, add a condition that checks whether the corresponding timestamp cell in A is blank before writing Now.
The procedure also clears the stamp if the value in B is cleared. Remove the Else branch if a cleared data cell should leave its previous timestamp in place. The event is disabled while the macro writes to column A, preventing the write from triggering another worksheet-change event. The cleanup label restores the prior event setting, including when an error occurs during the loop.
Choose a fixed value or a formula
A formula is convenient when you need a changing display, but it is usually not appropriate as a permanent audit-style stamp. With a date-time result in B2, enter =NOW() in the cell where you want the current date and time displayed. Format that cell as a date and time. NOW is volatile: Excel recalculates it when calculation occurs, so the displayed time can change after edits or recalculation. It is not a reliable way to preserve the time a particular row was first entered.
Use VBA when the value should be written once at a defined event, such as typing into a particular column. Use a formula when the desired result should remain current rather than record an event. For a manual fixed entry without VBA, Excel’s keyboard shortcuts can insert the current date or time as a value; they do not automatically combine both into one date-time entry in every setup.
- Fixed VBA value: stays unchanged unless edited, cleared, or replaced; suitable for a simple entry log.
=NOW()formula: recalculates and can display a later time; useful for a live current-time calculation, not a historical record.- Manual shortcut: quick for occasional entries, but requires a person to perform the action and may need separate date and time entries.
Format the timestamp for the information you need
The number format changes the display, not the stored timestamp. The sample format yyyy-mm-dd hh:mm:ss uses a four-digit year, month, day, 24-hour time, and seconds. For a shorter display, use a format such as m/d/yyyy h:mm AM/PM. If seconds are not important, omit :ss. In VBA, assign the chosen pattern to the cell’s NumberFormat property.
A cell can contain a time with seconds even when the format shows only hours and minutes. Conversely, showing seconds does not make the computer clock more accurate. If a timestamp appears as a number, apply a date-time number format. If it displays only a date, the format may be hiding the time rather than the value lacking it. Widening the column can also prevent a formatted date from appearing as a row of number signs.
For a sortable log, retaining a real Excel date-time value is generally more useful than converting it to text. A text stamp may look right but can sort alphabetically and may not work as expected in date calculations. Apply formatting to the numeric value instead of using VBA to turn the timestamp into a text string.
Common problems and limits
- The macro does not run: confirm the file is saved as
.xlsm, macros are permitted by your organization’s security settings, and the event code is in the worksheet module for the sheet being edited. Excel for the web cannot run VBA. - The event runs for the wrong edits or not at all: check that the edited column matches the code. A
Worksheet_Changeevent responds to direct cell changes, including many pasted values, but it does not run just because a formula’s calculated result changes. - The timestamp changes repeatedly: check whether the cell contains
=NOW()rather than a value written by VBA. Also check whether the event code intentionally refreshes the stamp on each edit. - Time is offset or inaccurate: VBA’s
Nowdepends on the computer’s date, time, and time-zone settings. Correct the system clock or use an appropriately designed time-zone process if a shared UTC reference is required. - The timestamp was accidentally overwritten: event macros can update cells without a convenient undo history. Keep a backup and test event code on a copy of the workbook before using it with important records.
For records where auditability matters, a worksheet timestamp alone is not tamper-proof: users with editing access can change cells or code, and a local computer clock can be wrong. Consider workbook permissions, version history, or a controlled record system appropriate to the purpose. A VBA timestamp is a helpful automation feature, not proof that an entry is legally or independently verified.
Frequently Asked Questions
How do I insert the current date and time in Excel VBA?
Assign Now to the destination cell, then set its NumberFormat to a date-time pattern such as yyyy-mm-dd hh:mm:ss. The first macro above writes the value to the selected cell.
How do I timestamp a row when I enter data?
Use a Worksheet_Change event in that sheet’s code module. Intersect the changed range with the input column, then write Now to the timestamp column on the same row. Disable events during the write and restore them afterward.
Does NOW() create a permanent timestamp?
No. =NOW() is a volatile formula and can recalculate to a later time. To preserve a time as a value, use VBA to write Now, or use a manual date/time entry method.
Can VBA record a timestamp when a formula result changes?
Not with Worksheet_Change alone, because a recalculated formula is not a direct edit to the cell. A Worksheet_Calculate event can respond to recalculation, but detecting which result changed and avoiding repeated stamps requires additional logic, such as storing and comparing prior values.
Can Excel for the web run a VBA timestamp macro?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not run VBA macros. Use desktop Excel to run VBA, or choose a non-VBA approach if the workflow must operate in a browser.
Can I undo a VBA timestamp with Ctrl+Z?
Generally, no. Running a macro clears Excel’s normal undo history, so Ctrl+Z cannot reliably reverse its changes. Save a separate copy and test the macro on sample data before applying it to an important workbook.
Related
- Excel Text to Columns: Split at Line Breaks
- Excel Text to Columns Greyed Out? Causes and Fixes
- Excel Text to Columns: 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
- Mouse Macro Recorder: How It Works and What to ChooseA mouse macro recorder is software or a device feature that records actions such as clicks, pointer movements,…
- Excel Macro Security Settings: What to Choose and WhyIn Excel for Microsoft 365 on Windows, go to File > Options > Trust Center > Trust Center…
- Excel VBA Database Connections: Setup, Code, and SafetyExcel VBA can connect to a database on Windows by using a data provider, opening a connection, and…
- VBA Sort Sheets Alphabetically: Macro and StepsYes. In Excel for Microsoft 365 on Windows, a short VBA macro can sort a workbook’s sheet tabs…
- Excel VBA Union: Combining Multiple Ranges in a Single MacroIn Excel VBA, Union combines two or more ranges on the same worksheet into one Range object, even…
- Excel VBA on Mac: How to Open Files with a MacroYes. Excel for Mac can open a workbook from VBA with Workbooks.Open; give it the file’s full Mac…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.