Tom’s Tutorials For Excel: Stamping Your Worksheet Changes With Date and Time

Tom’s Tutorials For Excel: Stamping Your Worksheet Changes With Date and Time

Suppose your worksheet requires frequent entries in a column, and you want to automatically record the date and time of whenever a cell in that column changes.



In the picture, when Forecast cells in range C5:C16 are changed, the date and time of that change instantly gets placed into column D on the same row.

This requires a Worksheet_Change event procedure. Right-click on your worksheet tab, left-click to select View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5:C16")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
End With
End Sub
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials For Excel: Stamping Your Worksheet Changes With Date and Time
  1. shoaib says:

    i have one sheet. i want if any data change in column1, column2, column3, and column4 so i can stamp date and time in column 5 ( means any data will change in the sheet stamp date shows up on column 5) please help me in this

    • Tom Urtis says:

      In your worksheet module, this would accomplish what you ask for:

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column > 4 Then Exit Sub
      If Target.Cells.Count > 1 Then Exit Sub
      With Cells(Target.Row, 5)
      .Value = Now
      .NumberFormat = "MMM DD, YYYY, hh:mm AM/PM"
      End With
      End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

*