How to insert current timestamp when data in another cell changes in Excel?
Supposing you receive a worksheet which needs you to modify data in a certain column. And after modifying the worksheet, you need to insert the modified timestamp to let others know which cells have been changed. How to deal with it? This article will show you method of inserting current timestamp when data in another cell changes in Excel as below screenshot shown.
Insert current timestamp when data in another cell changes with VBA code
As below screenshot shown, you need to populate timestamp in column E if corresponding cells in column C are changed. Please do as follows.
1. In the worksheet you need to modify and mark with timestamp, right click the sheet tab and then click View Code from the right-clicking menu.
2. Then copy and paste the below VBA code into the Code window of the Microsoft Visual Basic for Applications window. See screenshot:
VBA code: Insert current timestamp when data in another cell changes
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180830
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3
xTimeColumn = 5
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Now()
Else
On Error Resume Next
Set xDPRg = Target.Dependents
For Each xRg In xDPRg
If xRg.Column = xCellColumn Then
Cells(xRg.Row, xTimeColumn) = Now()
End If
Next
End If
End If
End Sub
Note: In the code, number 3 means that you are going to modify data in column C, and 5 indicates the timestamp will be populated into column E. Please change them based on your needs.
3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.
From now on, when changing data in column C, the timestamp will be populated into corresponding cells in column E as below screenshot shown.
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
Hi Max, You need to paste the code into the corresponding sheet's code window (as the below screenshot shown), after pasting the code, change the data and the date column numbers based on your needs. When return to the worksheet and change any cell value in the data column, the code will take effect automatically and populate the time stamp into the specified date column cell.
Hi, The below code can help. Please have a try. Thank you.
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20200529
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3
xTimeColumn = 5
xRow = Target.Row
xCol = Target.Column
If xCol = xCellColumn Then
If Target.Text <> "" Then Cells(xRow, xTimeColumn) = Now()
Else Cells(xRow, xTimeColumn) = ""
End If Else
On Error Resume Next Set xDPRg = Target.Dependents
For Each xRg In xDPRg If xRg.Column = xCellColumn Then
If Target.Text <> "" Then Cells(xRow, xTimeColumn) = Now()
Else Cells(xRow, xTimeColumn) = ""
End If End If
Next End If
End Sub
Thank you so much for your help thus far, your code works perfectly, I changed one line so it changes when a specific value is added in a cell now I have another idea, it would be amazing if you could help.
I want to track a manufacturing process. I have a drop down list where progress is selected as the product is manufactured. The code that I am thinking about goes as follows:
When the product is completed Column L gets changed to "Completed" and Column M gets a time stamp, when the product is delivered Column L changes again to "Delivered" and then Column N gets a timestamp.
Is this possible? Please let me know if I can send a screenshot if my message is unclear.
Enable iterative calculations under options > formula > "check" enable iterative calculations set the number to like 20
This should work... it will essentially make excel do a loop that it will stop calculating after 20 times through and leave a timestamp in the respective column.
The first one in column M will look like: "=IF(L1="Completed",IF(M1<>"",M1,NOW()),"")
The second formula will look the same except change "completed" to "delivered" and "M1" to "N1"
Many thanks for this - set it up yesterday and all worked perfectly. However I have updated cells today and the dates do no seem to update. I have also tried to duplicate the code on a second tab today and this is not working.
Hi Crystal,
I am trying to create a function where If I update any Cell in H9:L9 in Sheet 1, timestamp in cell C2 in Sheet 2 should get updated.
Now I need to do this on about 100 rows in Sheet 1( H10:L10, H11:L11) and update corresponding C3, C4....in Sheet 2
The above post shows the data that is input into a row. I was seeking help updating the first 2 columns that contain a timestamp whenever the ID (10000000) column is populated. If possible could the timestamps also be removed if the ID is also removed? Concerning the last 3 columns (date|time|staff) could those timestamps get updated whenever the last column is populated with a staff name. I tried playing around with the code but my lack of knowledge with VBA only allowed me to do so much.
It has been a month I am looking for a similar solution like this one. While the above solution seem to be OTM, I am using this for the stock market.
So I have Column F where I have implemented a Buy/Sell strategy and the sheet continues to refresh every 1 minute. I have Column T where I want the time stamp based on the following conditions :
(1) Column F - Signals a Buy or Sell
(2) Column F - Changes from a Buy to Sell or Sell to Buy
(3) Column F - Changes from a Buy/Sell to empty
While the general solution that is available on the net seem to be working (excel formula), but when the data refreshes it punches the current time than the signal time. For e.g. If I get a Buy @ 9:15 hrs and if the current time is 10:30, I get to see 10:30 hours in the Column T (Signal time) and not 9:15 hours.
What should be code to update the current time on the cell in column X if there is a change in any cell on that row? For example any change in values on cells a2 to w2, then the time should be to updated in x2.
Hi Crystal, this is really good, how can I get the timestamp change in each corresponding row when any cell from the range of cells in the respective row changes. For example, A2 should get updated with timestamp when any changes are done on second row between B2 to H2 and similarly A3 gets the timestamp when changes done on B3 to H3, and so on until the last row.
Adding the timestamp works very good and is very useful. However I receive an error when I delete a row, because the script cannot insert the timestamp in the row that does not exist anymore.
Would be great if someone can advice how to solve this.
I have a project I'm working on that I update regularly. It's mostly just for keeping track of my employee's information like phone numbers, drivers license expiration, etc. Since I update it so frequently, I want an automatic timestamp that rests at the top or bottom of my worksheet that will update with today's date everytime I update information ANYWHERE on the spreadsheet. I currently use A-N and 1-42 so I need a stamp around M40. How would that code look?
Is it possible to alter the code so that any changes in multiple columns would generate a new timestamp in the same cell? I.e. I make an edit in anyone of cells A3, B3 or C3 and the timestamp updates just in C4?
Also, is it possible to make the code apply only to a specific range within a sheet?
Hi Alec
Please try the below VBA code. Thank you for your comment.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRRg, xRg As Range
Dim xRgArray As Variant
Dim xStrCell As String
Dim xSCRg As Range
Dim xStrResult As String
xRgArray = Array("A1:C10")
xStrCell = "A3, B3, C3"
xStrResult = "C4"
On Error Resume Next
If Target.Cells.count > 1 Then Exit Sub
For xFNum = LBound(xRgArray) To UBound(xRgArray) If xRgArray(xFNum) <> "" Then
Set xRg = Range(xRgArray(xFNum)) If Not xRg Is Nothing Then
Set xSCRg = Null Set xSCRg = xRg.Range(xStrCell)
xSCRg.Select If Not xSCRg Is Nothing Then
If Not Intersect(xSCRg, Target) Is Nothing Then Set xRRg = xRg.Range(xStrResult)
If xRRg Is Nothing Then Exit Sub xRRg.Value = Now()
Exit For End If
End If End If
End If Next xFNum
End Sub
Hey, hopefully you still see this!!! I'm using the code below for a timestamp update in C6 anytime C5 is updated. The problem is that when I'm in the rest of the worksheet, anytime I hit enter or tab, it jumps me back to C5. Any thoughts?
Dim xRRg, xRg As Range
Dim xRgArray As Variant
Dim xStrCell As String
Dim xSCRg As Range
Dim xStrResult As String
xRgArray = Array("C5:C6")
xStrCell = "C5"
xStrResult = "C6"
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
For xFNum = LBound(xRgArray) To UBound(xRgArray)
If xRgArray(xFNum) <> "" Then
Set xRg = Range(xRgArray(xFNum))
If Not xRg Is Nothing Then
Set xSCRg = Null
Set xSCRg = xRg.Range(xStrCell)
xSCRg.Select
If Not xSCRg Is Nothing Then
If Not Intersect(xSCRg, Target) Is Nothing Then
Set xRRg = xRg.Range(xStrResult)
If xRRg Is Nothing Then Exit Sub
xRRg.Value = Now()
Exit For
End If
End If
End If
End If
Next xFNum
End Sub
This is exactly what I was looking for! However I was wondering if you can help me get it to work with a protected worksheet. It only seems to work on an unprotected one (when the timestamp updates due to a change in value as part of a formula, similar to Dennis' request).
Hi,
This can also work on a protected worksheet. You need to format the changed cells and the timestamp cells to unlocked status in advance before protecting the worksheet. Please have a try again. Thanks for your comment.
thanks so much for this, however the timestamp only changes if the value in the cell is changed "manually". If it's changed due to a change in another cell and the value changes as part of a formula, there is no timestamp. Do you have a solution for this? thanks! Dennis
Hi, This works perfectly if I manually change the cell, when I copy and paste information is not working. I'm trying to have this timestamp every time someone pastes new information. Rather than changing cell information one by one, the behavior I want to capture is when the data is pasted in bulk for multiple cells. Does somebody know what I should do?
Thank you so much for this! I use scroll buttons to add 1 to my total every time I complete a task, but macros don't register changes to the buttons. After doing some more research I figured out how to assign a macro to my control buttons and now I can track my changes!!!! I was making it more complicated than it need to be, this is what I ended up with:
Sub Timestamp()
'
' Timestamp Macro
' Timestamp on scroll buttons
'
'
Range("j2").Value = Now()
I've got a database in excel that I need to keep track of what has been changed.
Is it possible to modify the code so that if changes are made in sheet 1, it will duplicate the changed cell in sheet 2 and provide a time stamp beside it?
Good Day,
If you want to always display timestamp in cell A1 if any values are changed in a certain range. The following VBA code can help.
Private Sub Worksheet_Change(ByVal Target As Range) Dim xRg As Range
On Error Resume Next Set xRg = Intersect(Target, Range("B1:D4"))
If xRg Is Nothing Then Exit Sub Range("A1").Value = Now()
End Sub
hi Crystal, this is really good, how can I get the timestamp change in each corresponding row when any cell from the range of cells in the respective row changes. Like A2 should get updated with timestamp when any changes are done between B2 to H2 and so on with A3, A4 etc.
What is the code if I want to timestamp several cells individually in the same worksheet?
Example: Change in column 3, timestamp in column 5. Change in column 10, timestamp in column 11. Change in column 13, timestamp in column 14.
xCellColumn = 3
xTimeColumn = 5
Dear Mary,
The following VBA code can help you. Thank you for your comment.
Private Sub Worksheet_Change(ByVal Target As Range) Dim I As Long
Dim J As Long Dim xCols As Long
Dim xSRg As Range Dim xRgEx As Range
Dim xSRgArea As Range Dim xNextArea As Range
On Error Resume Next Application.EnableEvents = False
Set xSRg = Union(Range("C:C"), Range("E:E"), Range("J:K"), Range("M:N")) Set xRgEx = Intersect(xSRg, Target)
If xRgEx Is Nothing Then GoTo ExitSub For I = 1 To xSRg.Areas.Count
J = I Set xSRgArea = xSRg.Areas.Item(I)
If xRgEx.Column = xSRgArea(1).Column Then xCols = xSRgArea.Columns.Count
If xCols = 1 Then Set xNextArea = xSRg.Areas.Item(J + 1)
If xNextArea Is Nothing Then GoTo ExitSub
ElseIf xNextArea.Columns.Count = 1 Then xNextArea(xRgEx.Row).Value = Now()
Exit For End If
ElseIf xCols = 2 Then xSRgArea.Columns(2).Rows(xRgEx.Row).Value = Now()
Exit For End If
End If Next
ExitSub: Application.EnableEvents = True
End Sub
Can you share what needs to be updated to make it specific to another spreadsheet? For instance, changes in C timestamp in E, changes in G timestamp in I, changes in O timestamp in Q.
I am creating a work sheet in Excel where I want to track Start and End time of a particular transaction using a timestamp. A column will also show the time duration. Please see picture below. Can someone help me? Image below.
I found 2 VBA Codes that records time stamps. However, I don't know how to merge them so they will work in this sheet:
Code 1 . It shows timestamp in "Start Time" column (B4) when you enter a transaction number in "Case ID" coumn (A4).
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler
If Target.Column = 1 And Target.Value <> "" Then Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now(), "mm/dd/yyyy HH:mm:ss") Application.EnableEvents = True
End If Handler:
End Sub
Code 2. It shows time stamp in "End Time" column (D4) if a selection from a dropdown in "Status" column (C4) is selected.
Private Sub Worksheet_Change(ByVal Target As Range) Dim xCellColumn As Integer
Dim xTimeColumn As Integer Dim xRow, xCol As Integer
xCellColumn = 3 xTimeColumn = 4
xRow = Target.Row xCol = Target.Column
If Target.Text <> "" Then If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Now() End If
End If End Sub
I am pasting multiple rows and columns worth of data (at one time, and always at the same time). i'd only like for the time stamp to display in one cell. in other words, whenever cell C9 changes (the top left corner of my data), display the time in cell A1.
