How to record date and time automatically when cell changes?
It is easy for us to insert static date and time manually or insert a dynamic date changing with the system time with a formula. If you want to record the date and time automatically when you change or enter values, this problem may be somewhat different to deal with. But, in this article, you can solve this task with following steps.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
For example, I have a range of values, and now, when I change or type new values in Column B, I want there will automatically record current date and time in Column C as following screenshot shown:
You can finish this task with following VBA code. Please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Then choose your used worksheet from the left Project Explorer, double click it to open the Module, and then copy and paste following VBA code into the blank Module:
VBA code: Record date and time automatically when cell changes
Private Sub Worksheet_Change(ByVal Target As Range) 'Update 20140722 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. Then save and close this code to return to the worksheet, now when you change the cell value or type new data in Column B, the date and time will be recorded automatically in Column C.
1. In the above code, you can modify the “B:B” to any other column that you want to change the cell values in this script: Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target).
2. With this xOffsetColumn = 1 script, you can insert and update the date and time to the first column next to your changing value column, you can change the number 1 to other numbers, such as 2,3,4,5…that means the date will be inserted the second, third, fourth or fifth column besides your changed values column.
3. When you delete a value in the changed column, the date and time will be removed as well.