Column range is easy to change to specific cell but i don´t know how to get the timestamp in an specific cell :(
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.
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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.