Skip to main content

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

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.

Tip. Date Picker. The Insert Date utility of Kutools for Excel helps you to quickly insert date with specified date format into a selected cell.
Download and try it now! (30-day free trail)

Related articles:

Best Office Productivity Tools

Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolset:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 30-day free trial.

kte tab 201905

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!
Comments (59)
No ratings yet. Be the first to rate!
Huzefa
Huzefa
This comment was minimized by the moderator on the site
Hi OP,

Not sure if you are still seeing this. I am trying to update this so that the formula picks up a range. I want Col E:I to be updated with a timestamp if Col J:N update to "Completed". Do you have the code for that?

Thanks,
Huzefa A.
Reply
0
0
crystal
crystal     Huzefa
This comment was minimized by the moderator on the site
Hi Huzefa,

I don't know if I understand your question clearly. The following VBA code may help. Please give it a try.
The relationship is a 1:1 mapping between the columns. With this script, when a cell in column J is updated to "Completed", the timestamp will be inserted into the corresponding cell in column E. Similarly, updates to column K will affect column F, and so on.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20230816
    Dim IntersectRng As Range
    Dim TargetColumn As Integer

    ' Check if the changed cell is in columns J to N
    Set IntersectRng = Application.Intersect(Target, Me.Range("J:N"))

    If Not IntersectRng Is Nothing Then
        For Each cell In IntersectRng
            ' If the changed cell's value is "Completed"
            If cell.Value = "Completed" Then
                ' Calculate the target column in E:I
                TargetColumn = cell.Column - 5
                Me.Cells(cell.Row, TargetColumn).Value = Now()
            End If
        Next cell
    End If
End Sub
Reply
Report
0
0
Jon
Jon
This comment was minimized by the moderator on the site
How would I go about running this Macro in all of the sheets in my workbook? It is only working in the first sheet.
Reply
0
0
crystal
crystal     Jon
This comment was minimized by the moderator on the site
Hi Jon,
The following VBA code can help. Please give it a try.
Note: This VBA code needs to be added to the ThisWorkbook (Code) window.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/Thisworkbook.png
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Updated by Extendoffice 20221208
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
Reply
Report
0
0
Michael-1904
Michael-1904
This comment was minimized by the moderator on the site
Hallo !
Ich habe noch eine Frage zu dem oben angegebenen VBA-Code.
Bei meiner Tabelle befindet sich der Wert, für den der Zeitstempel erstellt werden soll in Spalte G.
Der Wert in G errechnet sich aus der Summe der Werte in den Spalten K:AA.
Die Werte in K:AA ergeben sich aus einem anderen Tabellenblatt, unter Benutzung der Zählenwenn-Funktion.
Wenn die Werte direkt in K:AA eingegeben werden aktualisiert sich der Zeitstempel wie gewünscht.
Wenn die Werte in K:AA durch "Zählenwenn" aus einem anderen Tabellenblatt aktualisiert werden, ändert sich der Zeitstempel nicht.

Hat jemand eine Lösung für mein Problem ?
Reply
Report
0
0
crystal
crystal     Michael-1904
This comment was minimized by the moderator on the site
Hello Michael Rasche,
The formula issue cannot be handled yet. Sorry for the inconvenience.
Reply
Report
0
0
Michael
Michael
This comment was minimized by the moderator on the site
Hallo,
ich möchte auch gerne in einer Tabelle einen Zeitstempel setzen, und bin bei der Suche auf diese Seite gestossen.
Ich habe alles wie oben beschrieben eingegeben, jetzt ergibt sich noch folgendes Problem:
Der Zeitstempel soll sich ändern, wenn sich der Wert in Spalte G ändert, der Wert in Spalte bildet sich aus der Summe mehrerer Zellen (z.B. K 150 - AA 150) in der Reihe.
Wenn ich einen neuen Wert direkt in die Zellen eingebe funktioniert das VBA.
Bei meiner Tabelle ändern sich die Werte in z.B. K 150 - AA 150 aber durch die "Zählenwenn" Funktion aus einem anderen Tabellenblatt, wenn sich dann die Summe erhöht, ändert sich der Zeitstempel nicht.
Hat jemand eine Lösung für mich ?
Reply
0
0
nate
nate
This comment was minimized by the moderator on the site
how do I add this on two columns? to have two timestamps for two column on input???
Reply
0
0
Gerald.First12
Gerald.First12
This comment was minimized by the moderator on the site
Hi, finally I found this post. Thankyou I Needed this. But can anyone help me, I want to change the code so that instead of tracking the changes on the current worksheet, I want the code to track the changes on selected Columns from other worksheet, then insert the timestamp to the current worksheet.
Reply
Report
0
0
Gerald
Gerald
This comment was minimized by the moderator on the site
Hi, finally i found this post. Thankyou I Needed this. But can anyone help me, I want to change the code so that instead of tracking the changes on the current worksheet, I want the code to track the changes on selected Columns from other worksheet, then insert the timestamp to the current worksheet.
Reply
0
0
Paul
Paul
This comment was minimized by the moderator on the site
Hi, This is great info, but I'm having problem. I would like to use for change in column A time stamp column B and change in column D with time stamp column E and change in column G with time stamp column H. I can not figure out. Help please.
Reply
0
0
Lina
Lina
This comment was minimized by the moderator on the site
Hi, Is there a chance you can help me? I have tried the VBA code for the timestamp:
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

My problem is that I have to add dates in column 22 and I need the timestamp in column 21. The above code does not work in my sheet. I have tried it with timestamp functions and formulas, but it always changes it to the date when I opened the workbook.
I also tried to work with conditional formatting that the another cell changes the colour when the cell with the timestamp is not todays date, but this does not work either. Can you let me know what I do wrong?
Reply
0
0
crystal
crystal     Lina
This comment was minimized by the moderator on the site
Hi Lina,
Do you mean manually enter dates in column 22 and then populate timestamp automatically in adjacent cells in column 21?
You just need to change the column numbers in the code to get it down. If you only want to display time, replace the NOW() with Time() in the code. See the below code.

<div data-tag="code">Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20201106
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 22
xTimeColumn = 21
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Time()
Else
On Error Resume Next
Set xDPRg = Target.Dependents
For Each xRg In xDPRg
If xRg.Column = xCellColumn Then
Cells(xRg.Row, xTimeColumn) = Time()
End If
Next
End If
End If
End Sub
Reply
Report
0
0
Lina
Lina     crystal
This comment was minimized by the moderator on the site
Thank you for your response. Yes, that's what I wanted to do. I tried the above VBA code before but for some reason it did not work, but it works fine now. Thank you for your help
Reply
0
0
Max
Max
This comment was minimized by the moderator on the site
I have copied this code into VBA, but no dates are being added to the column. How do I run this macro/sub?
Reply
0
0
crystal
crystal     Max
This comment was minimized by the moderator on the site
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.
View Attachments (1 / 5)
Reply
Report
0
0
NannyKate
NannyKate
This comment was minimized by the moderator on the site
Looking to enter data in columns D, E & F with a time stamp in the respective row in column G
Reply
0
0
Baltimorejack68
Baltimorejack68
This comment was minimized by the moderator on the site
Is there a way to clear the time stamp if the content of column C is deleted?
Reply
Report
0
0
crystal
crystal     Baltimorejack68
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
Erich
Erich
This comment was minimized by the moderator on the site
Hi Crystal,

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.

Thank you so much, hope you can help.
Reply
Report
0
0
Kevin
Kevin     Erich
This comment was minimized by the moderator on the site
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"
Reply
0
0
Hannah
Hannah
This comment was minimized by the moderator on the site
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.

Any ideas?
Reply
0
0
crystal
crystal     Hannah
This comment was minimized by the moderator on the site
Hi,
Have you saved the workbook as an Excel Macro-Enabled Workbook?
The code works well in my case.
Reply
Report
0
0
shri
shri
This comment was minimized by the moderator on the site
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

Any help is greatly appreciated. Cheers.!
Reply
Report
0
0
Luis.nsv
Luis.nsv
This comment was minimized by the moderator on the site
5/15/19|1:41 PM|John Smith|125B-1|10000000|Equipment|Staff|5/15/19|2:43 PM|Staff

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.
Reply
Report
0
0
JediTrader
JediTrader
This comment was minimized by the moderator on the site
Gents,

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.

Any assistance would be greatly appreciated.

Warm Regards
JT
Reply
Report
0
0
spen
spen
This comment was minimized by the moderator on the site
Would the timestamp functionality be bypassed if the excel file you're using is a refreshable?
Reply
0
0
crystal
crystal     spen
This comment was minimized by the moderator on the site
Good day,
The code does not support connections data. Sorry for the inconvenience.
Reply
Report
0
0
sh.senthilkumar@gmail.com
This comment was minimized by the moderator on the site
Hello Everyone

Thanks in advance for your help.

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.


Thanks Again.
Reply
Report
0
0
Farid
Farid
This comment was minimized by the moderator on the site
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.
Reply
0
0
Sander
Sander
This comment was minimized by the moderator on the site
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.
Reply
0
0
crystal
crystal     Sander
This comment was minimized by the moderator on the site
Hi Sander,
There is no error occur in my case. Can you tell me your Excel version? Thank you for your comment.
Reply
Report
0
0
Jessica
Jessica
This comment was minimized by the moderator on the site
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?
Reply
0
0
Tychabrahe
Tychabrahe     Jessica
This comment was minimized by the moderator on the site
Open Developer and on your workbook select the Workbook and the BeforeSave event.


In the sub put the code: Range("M40").value = Now
Reply
0
0
Alec
Alec
This comment was minimized by the moderator on the site
Hi,


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?


Thanks
Reply
0
0
crystal
crystal     Alec
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
Rachel
Rachel     crystal
This comment was minimized by the moderator on the site
Hey, this is sort of similar to what I require, i'd like for a timestamp to be updated in column AG when cells between A and AH are updated.
I've tried the above code but have had no luck.
Reply
0
0
Blaze
Blaze     crystal
This comment was minimized by the moderator on the site
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
Reply
0
0
basselo
basselo
This comment was minimized by the moderator on the site
Hi,

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).

Thank you so much! Bassel
Reply
Report
0
0
crystal
crystal     basselo
This comment was minimized by the moderator on the site
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.
Reply
Report
0
0
Dennis
Dennis
This comment was minimized by the moderator on the site
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
Reply
0
0
crystal
crystal     Dennis
This comment was minimized by the moderator on the site
Dear Dennis,
The code has been updated with the problem solved. Thanks for your comment.
Reply
Report
0
0
Diego
Diego
This comment was minimized by the moderator on the site
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?


Thanks,
Reply
0
0
Leslie
Leslie
This comment was minimized by the moderator on the site
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()

End Sub
Reply
0
0
Amelius
Amelius
This comment was minimized by the moderator on the site
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?
Reply
Report
0
0
crystal
crystal     Amelius
This comment was minimized by the moderator on the site
Good day,
Sorry can't help with that. Thank you for your comment.
Reply
Report
0
0
suresh.thangaiyan83@gmail.com
This comment was minimized by the moderator on the site
Hi, can be done if any values are changed and the timestamp is updated only in a particular cell like A1
Reply
Report
0
0
crystal
crystal    
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
Farid
Farid     crystal
This comment was minimized by the moderator on the site
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.
Reply
0
0
Mary
Mary
This comment was minimized by the moderator on the site
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
Reply
0
0
crystal
crystal     Mary
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
britni.norris@gmail.com
    crystal
This comment was minimized by the moderator on the site
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.
Reply
Report
0
0
W D
W D    
This comment was minimized by the moderator on the site
I simply strung the first code mentioned above together, changing all the variables from VAR to VAR1/VAR2/VAR3 etc
Reply
0
0
hotkya
hotkya
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
crystal
crystal     hotkya
This comment was minimized by the moderator on the site
Dear chia,
Did not see your image. Please click the below Upload files button to upload your picture.
View Attachments (1 / 5)
Reply
Report
0
0
november
november
This comment was minimized by the moderator on the site
how can we lock the time format.. cuz it updates everytime and inserts date and time.
Reply
0
0
eric
eric
This comment was minimized by the moderator on the site
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. any help would be much appreciated!
Reply
0
0
Abdul
Abdul
This comment was minimized by the moderator on the site
Hi, The above formula will be useful for single cell time stamp update Ex: If we enter any data in 3rd cell, time stamp automatically updated in 5th cell. Please advice for multiple cell update. Ex: If we enter any data in 3rd cell, time stamp automatically updated in 5th cell and if we enter on 4th cell, it will update on 6th cell.
Reply
0
0
Tyler
Tyler     Abdul
This comment was minimized by the moderator on the site
I too am interested in this exact question. Have you found an answer? Will my commenting on this draw anyone else's attention to the question?!
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations