How to Automatically Update Current Timestamp When another Cell Is Changed

This post will guide you how to automatically update cell with current timestamp when another cell is changed in Excel. How do I insert date and time in adjacent cell automatically when changing cell values in Excel.

Insert Date and Time Automatically When Cell Changes

Assuming that you have a list of data in range B1:B6, and when a cell changes I would like the cell in the adjacent cell in the next column to record the date and time of this change. Once another cell is changed, and the timestamp in the adjacent cell should also be changed automatically. How to do it. You can use an Excel VBA macro to achieve the result. Here are the steps:

#1 right click on the current worksheet tab, and select View Code from the popup menu list. And the Microsoft Visual Basic for Applications window will appear.

auto update date and time when cell change1

#2 paste the below VBA code into the code window. Then clicking “Save” button.

auto update date and time when cell change2

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    If Not Intersect(Target, Range("B1:B6")) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Target
            cell.Offset(0, 1).Value = Now
            cell.Offset(0, 1).NumberFormat = "dd/mm/yyyy, hh:mm:ss"
        Next cell
    End If
    Application.EnableEvents = True
End Sub

#3 you can try to change cells in range B1:B6, and you should notice the timestamp should be inserted into the adjacent cell.

auto update date and time when cell change3

Note: you need to modify the range B1:B6 as you need.


Leave a Reply