Note: The other languages of the website are Google-translated. Back to English
English English

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.

Record date and time automatically when cell changes with VBA code

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • 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.

arrow blue right bubble Record date and time automatically when cell changes with VBA code


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:

doc-update-time-value-changes-1

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

doc-update-time-value-changes-1

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.

Notes:

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.


The Best Office Productivity Tools

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...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • 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.
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!
officetab bottom
Comments (108)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How do you write the code to do this multiple times on one page Ex. text in column B, date in C and text in D, date in E?
This comment was minimized by the moderator on the site
[quote]How do you write the code to do this multiple times on one page Ex. text in column B, date in C and text in D, date in E?By Hilary[/quote] I know this is old but, the long, yet easy way, is to copy the variable declarations and create new variables (i.e. WorkRng1 and Rng1). You also have to copy the "if" statement and change the variables to the new variable names.
This comment was minimized by the moderator on the site
[quote]How do you write the code to do this multiple times on one page Ex. text in column B, date in C and text in D, date in E?By Hilary[/quote] Here is the code: Private Sub Worksheet_Change(ByVal Target As Range) 'Update 20140722 Dim WorkRng As Range Dim Rng As Range Dim xOffsetColumn As Integer 'Change the Range to select the column you are updating, i.e. ("A:A") or ("B:B") Set WorkRng = Intersect(Application.ActiveSheet.Range("E:E"), Target) xOffsetColumn = 1 'For the second time stamp 'Create new variable names, such as WorkRng1, Rng1, etc. Dim WorkRng1 As Range Dim Rng1 As Range Dim xOffsetColumn1 As Integer Set WorkRng1 = Intersect(Application.ActiveSheet.Range("G:G"), Target) xOffsetColumn1 = 1 'For the first time stamp 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 = "mm/dd/yyyy" Else Rng.Offset(0, xOffsetColumn).ClearContents End If Next Application.EnableEvents = True End If 'For the second time stamp If Not WorkRng1 Is Nothing Then Application.EnableEvents = False For Each Rng1 In WorkRng1 If Not VBA.IsEmpty(Rng1.Value) Then Rng1.Offset(0, xOffsetColumn1).Value = Now Rng1.Offset(0, xOffsetColumn1).NumberFormat = "mm/dd/yyyy" Else Rng1.Offset(0, xOffsetColumn1).ClearContents End If Next Application.EnableEvents = True End If End Sub
This comment was minimized by the moderator on the site
What if I want to do this for a range of columns?
This comment was minimized by the moderator on the site
change the range (B:B) like this: (10:20) for columns 10 to 20 (D5:D40) for columns 5 to 40 on row D Hope it helps.
This comment was minimized by the moderator on the site
Sorry, I swap columns with rows, but it still works. (D:P) From column D to P (D5:D5) from column D to P on row 5
This comment was minimized by the moderator on the site
when i save it work but when i reopen doesnt work
This comment was minimized by the moderator on the site
[quote]when i save it work but when i reopen doesnt workBy Aarif[/quote] Same problem as Aarif... It worked a treat half an hour ago, and now it doesnt... also, I wanted only the time not the date AND time, so I editted the ".NumberFormat" to be just hh:mm:ss, saved, tested it... no different... still putting the date and the time...? opened the VB again, and typed in some delimitted text, saved again, still no good then I did it a third time, and the format did as it was told and suddenly I got just the time. VERY weird I have long thought that Excell is haunted, ever since I first used the SEARCH function, searching for a value in a cell that I could see with my own eyes, but Excel insisted it didnt exist!? Any Excel gurus or exorcists that can help? why is it ignoring my VB and NOT timestamping as it did...? thank you :cry:
This comment was minimized by the moderator on the site
[quote]How do you write the code to do this multiple times on one page Ex. text in column B, date in C and text in D, date in E?By Hilary[/quote] what is the answer to the quote
This comment was minimized by the moderator on the site
What if I want this to run but am not on an active sheet, so run in the background and update whenever there is an entry into the B column?
This comment was minimized by the moderator on the site
What if the column that is effecting the time stamp column is the sum of multiple other columns? For instance... Column H is =Sum(E+F+G) and I would like a time stamp done in Colum I every time Column H has been changed, or in essence when Columns E, F, or G have been changed. Can you please help??
This comment was minimized by the moderator on the site
I need help please! I am trying to have a time stamp done each time that one of three cells is changed. I would ideally like to do this when the sum of these cells has been changed. For example. Cells E, F, G will be changed and Cell H will be the SUM of those three cells. When Cell H has been changed I would like for Cell I to be time stamped. Is this possible?? Thank you SO MUCH
This comment was minimized by the moderator on the site
I am attempting to run the above code; however, the cell who's change I want to record is linked to a checkbox and is therefore either "TRUE" or "FALSE". For some reason, the code will not work when the cell changes. However, if I insert say "yes" manually, the code works fine. Any workaround for this issue that you are aware of? Thank you!
This comment was minimized by the moderator on the site
Is it possible to do this for historical data? If I made changes to a cell yesterday or a month ago, can I use a form of this code to retrieve those dates?
This comment was minimized by the moderator on the site
Is there a way to time stamp based on a specific change? For example, I have a spreadsheet with a column of drop-down options including being blank, 'Ok', and 'Act'. When the task is completed, the drop down is manually used to select 'Ok'. I'd like the date stamp to be tied to when 'Ok' is selected, but not when 'Act' is selected. The 'Act' option in the drop down is used when a task is past due, but I'm really just trying to track when an item is completed. Is there a way to accomplish this with the above code? Thanks!
This comment was minimized by the moderator on the site
Were you able to resolve this?
This comment was minimized by the moderator on the site
it's very good question. have you sorted it out, could you please share the way?
thanks
This comment was minimized by the moderator on the site
I can change the range to be ("A:Q") but I need the date and time to go in "S" if the data in any A-Q cell is changed/removed. I'm VBA challenged. Any help is greatly appreciated!
This comment was minimized by the moderator on the site
Sorry, I just posted a question and I need the answer to this...I must have missed this comment...hopefully someone can help!
This comment was minimized by the moderator on the site
How do I change it so only the adjacent cell date changes?
This comment was minimized by the moderator on the site
How do I get the time stamp to record in a specific cell, instead of the cell next to the one that was changed. I am attempting to have a row of cells time stamped when a change occurs in any of those cells, I would like the time stamp to record in one specific cell...can anyone help with this? Thank you!
This comment was minimized by the moderator on the site
Hi , Thank you kindly for this as it is great for WB auditability. Is there a way of altering this to have the date/time be logged somewhere on the WB whenever a comment is added or modified? I know this is a general question, but I am not sure if it's possible (unfortuantely for MS 2010, the .comment property does not hold a date property to identify when it was entered. Mike
This comment was minimized by the moderator on the site
How can i configure to read the A:A column and give time stamp in B:B only when a 14 digit number is entered in it ?
This comment was minimized by the moderator on the site
Hi, I need some VBA coding help. I am creating a spread sheet with cases which will be assigned to people and have case status. The assignee's will be in Col H and I need a date stamp in Col I when the cell in Col H get updated or changed with a name. Similarly I will have case status in Col J and will need a date stamp in Col K when a status get inputted or changed. also when there is non assignee name or status I need the date stamp in Col I and K to be blank. Any help would be appreciated. Thanks
This comment was minimized by the moderator on the site
I found this code elsewhere and modified it for my use. If done properly, you do not need to worry about entering a function in a cell or the file changing the dates to that day's date every time it is opened. - open Excel - press "Alt+F11" - Double-click on the worksheet that you want to apply the change to (listed on the left) - copy/paste the code below - adjust the Range(_:_) input to correspond to the column you will update - adjust the Offset(0,_) input to correspond to the column where you would like the date displayed (in the version below I am making updates to column D and I want the date displayed in column F, hence the input entry of "2" for 2 columns over from column D) - hit save - repeat steps above if there are other worksheets in your workbook that need the same code - you may have to change the number format of the column displaying the date to "General" and increase the column's width if it is displaying "####" after you make an updated entry Copy/Paste Code below: ____________________________________________________________ Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub Target.Offset(0, 2) = Date End Sub ____________________________________________________________ Good luck...
This comment was minimized by the moderator on the site
this code will show date on click it appears, is it possible to show date only when content in cell is changed, if only clicked and nothing changed, then date does not show or previously entered date stays?
This comment was minimized by the moderator on the site
When i am record in macro a vloocup formula so how can i do that date automatically changed to 1 day up Like its 21/07/2017 then next day its changed to 22/07/2017 Automatically pls share if is there any vba code.
This comment was minimized by the moderator on the site
replace this line
Rng.Offset(0, xOffsetColumn).Value = Now

to
Rng.Offset(0, xOffsetColumn).Value = Now+1
This comment was minimized by the moderator on the site
Thank you so much for the code! Exactly what I was looking for!!
This comment was minimized by the moderator on the site
Thank you for your help :-)
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations