Keep a Static Timestamp When an Excel Cell Changes
Question details
Record a static date and time in column A when a value is entered in column J, preserve it when the value is edited, and clear it when the value is deleted.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A worksheet uses column J for entries and column A to record when each entry was first added.
- Observed behavior
- Each column A timestamp should remain unchanged while its column J entry is edited and disappear when that entry is cleared.
Open the workbook in desktop Excel and save it as an Excel Macro-Enabled Workbook (.xlsm). Make sure column A is reserved for timestamps and that existing entries in column J already have timestamps in column A if they need to be preserved.
Use a worksheet change event to maintain timestamps
Use this VBA event to create and clear timestamps automatically for edits or pasted values in column J.
The macro uses an empty cell in column A to identify an entry that needs a timestamp. It does not check the previous value in column J, so manually clearing a timestamp in column A can cause a new timestamp to be created on the next edit.
The event responds to direct worksheet changes, including multi-cell pastes. It does not run when a formula result changes only because the workbook recalculates.
In desktop Excel, right-click the relevant worksheet tab and select View Code. If the worksheet already has a Worksheet_Change procedure, incorporate the logic into that procedure rather than adding a second one.
Paste this code into the worksheet's code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim changedCells As Range Dim cell As Range Dim isBlank As Boolean Set changedCells = Intersect(Target, Me.Columns("J")) If changedCells Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False For Each cell In changedCells.Cells If IsError(cell.Value2) Then isBlank = False Else isBlank = (Len(CStr(cell.Value2)) = 0) End If If isBlank Then Me.Cells(cell.Row, "A").ClearContents ElseIf IsEmpty(Me.Cells(cell.Row, "A").Value) Then Me.Cells(cell.Row, "A").Value = Now Me.Cells(cell.Row, "A").NumberFormat = "yyyy-mm-dd hh:mm:ss" End If Next cell CleanUp: Application.EnableEvents = True End Sub
Save the .xlsm workbook and enable macros if prompted for a trusted file. Enter a value in a blank J cell, edit it, and then delete it; check that the corresponding A cell is timestamped, remains unchanged during the edit, and is cleared on deletion.

Work with Excel-compatible spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for everyday spreadsheet work. Its familiar interface and Excel format compatibility make it easy to move existing files; the Excel-specific VBA event above is not a guaranteed fix in WPS.

Frequently Asked Questions
Where should I paste the timestamp macro?
Right-click the worksheet tab in desktop Excel, select View Code, and paste the procedure into that worksheet's code window, not a standard module.
Will editing an existing value in column J change its timestamp?
No. The macro writes a timestamp only when the corresponding column A cell is empty. It preserves an existing timestamp when the J value is edited.
Does the macro work when I paste several cells into column J?
Yes. The procedure loops through all changed cells in column J, setting or clearing the corresponding column A cells as needed.
Why is the timestamp not changing when a formula recalculates?
Worksheet_Change responds to direct cell changes, not recalculation alone. A formula-driven timestamp requires a different approach, such as a carefully designed calculation event.
How do I keep the macro when I save the workbook?
Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). Enable macros only when you trust the workbook.




