How to record the date and time automatically when a cell changes?
In daily Excel work, it's quite straightforward to insert static date and time values by hand, or to use formulas to display a dynamic date that updates according to the system clock. However, if your goal is to automatically record the exact date and time whenever a cell changes or a new value is entered—essentially creating a timestamp of edits—this requires a different approach. Automatic timestamps are especially useful in scenarios such as tracking changes in data logs, monitoring updates in order tracking sheets, or recording input in collaborative spreadsheets for audit purposes. While Excel doesn't have a direct built-in function for capturing edit timestamps on value changes, there are several practical workarounds. In this article, you'll find a range of solutions from VBA coding to special formula techniques to help you achieve automatic date and time stamping when cell values change.
Record date and time automatically when a cell changes with VBA code
Record date and time automatically when a cell changes with VBA code
Suppose you have a table of values where you need to track updates as they occur. For instance, the current date and time should automatically appear in Column C whenever a value in Column B is changed or a new entry is made. This application is common for attendance records, inventory logs, or any workflow where timestamping edits is crucial for tracking history, accountability, or compliance.
To automate this kind of logging, you can use a simple VBA macro tailored for your worksheet. Unlike manual timestamp entry, this VBA-based method ensures the record is captured every time a change is made, reducing manual work and minimizing errors from omission. However, do note that VBA solutions require users to enable macros and save the file in a macro-enabled format (.xlsm), and VBA code will only run in environments where macros are permitted.
Follow these steps to implement the automatic timestamp feature using VBA code:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the VBA window, select your target worksheet from the left Project Explorer. Double-click on the sheet to open the code window for that worksheet. Then, copy and paste the following VBA code into the blank module area:
VBA code: Record date and time automatically when cell changes
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20250722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub
3. After entering the code, save your changes and close the VBA editor. Return to the Excel worksheet. Now, every time you modify or enter new data into a cell in Column B, the adjacent cell in Column C will automatically be updated with the current date and time.
Notes and tips:
- Customize the column: You can adjust the column where changes are monitored by editing the Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target) line in the script. For example, change "B:B" to another column as needed.
- Column offset explained: The line xOffsetColumn =1 determines which column will receive the date/time value. Changing this number will insert the timestamp further to the right of your changed value column (for example, 2 for two columns away, 3 for three columns, etc.).
- Deletion behavior: If a value is deleted in the monitored column, the corresponding timestamp in the adjacent cell will be deleted automatically as well.
- Error avoidance: Be sure to save your workbook in a macro-enabled format (
.xlsm), and remember to enable macros when opening the workbook to ensure the automatic timestamp works. - Compatibility reminder: VBA solutions may not function as expected if macros are disabled or on some cloud-based Excel platforms (such as Excel for the Web).
This method is very reliable for most desktop Excel users and is well-suited for moderate-sized datasets where audit trails are important. If you need timestamps in a more formula-driven or accessible way (without using macros), consider the approaches described below.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in