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

How to auto populate date in cell when adjacent cell is updated in Excel?

Sometimes, when update a cell in a certain column, you may want to mark the latest date about the updating. This article will recommend a VBA method to solve this problem. When cell is updated, the adjacent cell will be auto populated with current date immediately.

Auto populate current date in cell when adjacent cell is updated with VBA code

Auto populate current date in cell when adjacent cell is updated with VBA code

Supposing the data you need to update locates in column B, and when cell in column B is updated, the current date will be populated in the adjacent cell of column A. See screenshot:

You can run the following VBA code to solve this problem.

1. Right click the sheet tab you need to auto populate date based on the adjacent updated cell, and then click View Code from the right-clicking menu.

2. In the Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the Code window.

VBA code: auto populate current date in a cell when the adjacent cell is updated

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updated by Extendoffice 2017/10/12
    Dim xRg As Range, xCell As Range
    On Error Resume Next
    If (Target.Count = 1) Then
        If (Not Application.Intersect(Target, Me.Range("B:B")) Is Nothing) Then _
            Target.Offset(0, -1) = Date
        Application.EnableEvents = False
        Set xRg = Application.Intersect(Target.Dependents, Me.Range("B:B"))
        If (Not xRg Is Nothing) Then
            For Each xCell In xRg
                xCell.Offset(0, -1) = Date
            Next
        End If
        Application.EnableEvents = True
    End If
End Sub

Notes:

1). In the code, B:B means that the updated data locates in column B.
2). -1 indicates that the current date will be populated on the left one column of column B. If you want the current date populates in column C, please change -1 to 1.

3. Press Alt + Q keys at the same time to close the Microsoft Visual Basic for Applications window.

From now on, when updating cells in column B, the adjacent cell in column A will be populated with current date immediately. See screenshot:

Related articles:

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 (46)
No ratings yet. Be the first to rate!
Jishnu
Jishnu
This comment was minimized by the moderator on the site
Thank You. This helped a lot. But when I am deleting a row or adding a row this gives an Run-time error 13 Type mismatch. How to tackle this issue. Expecting a revert ASAP.
Reply
0
0
crystal
crystal     Jishnu
This comment was minimized by the moderator on the site
Dear Jishnu,
The problem you mentioned does not appear in my case. Would you please provide your Office version?
Reply
Report
0
0
leah
leah
This comment was minimized by the moderator on the site
I have a set of excel worksheets titled monday, tuesday, wednesday, etc. I need to put the beginning date on Monday in cell a1, and have it fill in the subsequent dates for tuesday through Friday in cell a1 on each of those sheets. I am not code literate at all, so I just need to know what the simple formula I can put in there is. :) thank you!
Reply
0
0
crystal
crystal     leah
This comment was minimized by the moderator on the site
Dear leah,
You just have to use the formula =monday!A1+1 on sheet tuesday, =tuesday!A1+1 on sheet wednesday and so on...
Reply
Report
0
0
Tammy
Tammy
This comment was minimized by the moderator on the site
Can this be accomplished on a cell that contains a formula?
Reply
0
0
crystal
crystal     Tammy
This comment was minimized by the moderator on the site
Dear Tammy,
The code has been updated. It can be accomplished on a cell that contains a formula now. Thank you for your comment.
Reply
Report
0
0
Michael
Michael
This comment was minimized by the moderator on the site
Thanks it works fine, but when it comes to close and save I'm getting a error that the feature "VB project" cannot be saved in a macro-free workbook. Please advise
Reply
0
0
Michael
Michael
This comment was minimized by the moderator on the site
Thanks it works fine, but when it comes to close and save I'm getting a error that the feature "VB project" cannot be saved in a macro-free workbook. Please advise
Reply
Report
0
0
Jay
Jay     Michael
This comment was minimized by the moderator on the site
You just need to "save as" a excel macro-enabled workbook....
Reply
Report
0
0
Sarah
Sarah
This comment was minimized by the moderator on the site
Are you able to use this function twice on the same sheet? i.e. if I would like to make entries in column B to time stamp column A AND put entries in column D to timestamp column C. Thanks!
Reply
0
0
John
John     Sarah
This comment was minimized by the moderator on the site
I got this to work by doing the following:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updated by Extendoffice 2017/10/12
Dim xRg As Range, xCell As Range
On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("B:B")) Is Nothing) Then _
Target.Offset(0, -1) = Date
Application.EnableEvents = False
Set xRg = Application.Intersect(Target.Dependents, Me.Range("B:B"))
If (Not xRg Is Nothing) Then
For Each xCell In xRg
xCell.Offset(0, -1) = Date
Next
End If
Application.EnableEvents = True
End If


On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("D:D")) Is Nothing) Then _
Target.Offset(0, -1) = Date
Application.EnableEvents = False
Set xRg = Application.Intersect(Target.Dependents, Me.Range("D:D"))
If (Not xRg Is Nothing) Then
For Each xCell In xRg
xCell.Offset(0, -1) = Date
Next
End If
Application.EnableEvents = True
End If
End Sub
Reply
0
0
sri laqshya
sri laqshya
This comment was minimized by the moderator on the site
Hi,

Can anyone one suggest a code for when I input number in column A and Column B and in Column C I kept a formula like Column A + Column B. Now I need a vba code that can give time and date in Column D whenever Column C changes not when inserting numbers in Column A and B.
Reply
Report
0
0
crystal
crystal     sri laqshya
This comment was minimized by the moderator on the site
Good Day,
Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
You will get more supports about Excel from our Excel professional.
Reply
Report
0
0
Jen
Jen
This comment was minimized by the moderator on the site
HI - new to VBA - I want to loop the code - VBA code: auto populate current date in a cell when the adjacent cell is updated so that after a cell is updated with a date, then move on to say "J:J" and update K with date and then do 2 more times. Not sure if it is a loop or for? Thank you
Reply
0
0
Jen
Jen
This comment was minimized by the moderator on the site
Nevermind...I tried John's response below and it works - thank you!
Reply
0
0
Tracey
Tracey
This comment was minimized by the moderator on the site
I used this code to auto populate a column and now wish to auto populate more columns based on date entered into column H. In other words, once a date is entered can I auto populate columns to add date 90, 60, and 30 days out?
Reply
Report
0
0
crystal
crystal     Tracey
This comment was minimized by the moderator on the site
Hi Tracey,
The following VBA code can help you solve the probem. Please have a try.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim xRg As Range, xCell As Range
Dim xRgAddress As String
xRgAddress = "H:H"
On Error Resume Next
If (Target.count = 1) Then
If (Not Application.Intersect(Target, Me.Range(xRgAddress)) Is Nothing) Then
Target.Offset(0, 1) = Date + 90
Target.Offset(0, 2) = Date + 60
Target.Offset(0, 3) = Date + 30
End If
Application.EnableEvents = False
Set xRg = Application.Intersect(Target.Dependents, Me.Range(xRgAddress))
If (Not xRg Is Nothing) Then
For Each xCell In xRg
xCell.Offset(0, 1) = Date + 90
xCell.Offset(0, 2) = Date + 60
xCell.Offset(0, 3) = Date + 30
Next
End If
Application.EnableEvents = True
End If
End Sub
Reply
Report
0
0
Gwen
Gwen
This comment was minimized by the moderator on the site
Hi. I'm trying to use the "Auto Populate Current Date In Cell When Adjacent Cell Is Updated With VBA Code". This works on Sheet 1, but I have 11 other sheets I need the code to work on. Please advise. I'm not VBA saavy at all so I appreciate any help I can get!

Thanks.
Reply
Report
0
0
crystal
crystal     Gwen
This comment was minimized by the moderator on the site
Hi Gwen,
Please repeat the steps to copy the code to other sheets. It may be tedious, but it works.
Reply
Report
0
0
Joe
Joe
This comment was minimized by the moderator on the site
Hello, I need some help. I am trying to populate the current date into column A if any cells B through N are modified. The offset is throwing me off. Can this code be written to auto populate the date in Column A specifically?
Reply
Report
0
0
crystal
crystal     Joe
This comment was minimized by the moderator on the site
Hi Joe,
The below VBA code can help you solve the problem. Please have a try and thank you for your comment.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updated by Extendoffice 20190924
Dim xRg As Range, xCell As Range
Dim xInt As Integer
On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("B:N")) Is Nothing) Then
Application.EnableEvents = False
xInt = Target.Row
Me.Range("A" & xInt).Value = Date
Application.EnableEvents = True
End If
End If
End Sub
Reply
Report
0
0
Abbey
Abbey
This comment was minimized by the moderator on the site
Hello, Can I set the date and the time to populate?
Reply
0
0
crystal
crystal     Abbey
This comment was minimized by the moderator on the site
Hi Abbey,
In the above code, please replace this line
"xCell.Offset(0, -1) = Date"
with
"xCell.Offset(0, -1) = Format(Now(), "yyyy-MM-dd hh:mm:ss")".
Hope I can help. Thank you for your comment.
Reply
Report
0
0
Ryan
Ryan     crystal
This comment was minimized by the moderator on the site
this didnt add the time in... is there another way?
Reply
0
0
Rizwan
Rizwan     Ryan
This comment was minimized by the moderator on the site
you have to change it at 2 places, if you see original code, date is at 2 places, change both with Format(Now(), "yyyy-MM-dd hh:mm:ss")
Reply
0
0
Harry
Harry
This comment was minimized by the moderator on the site
Hello, is there a way to make column (A) automatically populated with a date when entering a value into any cell in the same row?
Reply
0
0
crystal
crystal     Harry
This comment was minimized by the moderator on the site
Hi Harry,
The below VBA code can help you solve the problem. Please specify the range as much as possible in the code. Thank you for your comment.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updated by Extendoffice 20191017
Dim xRg As Range, xCell As Range
Dim xInt As Integer
On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("B:BP")) Is Nothing) Then
Application.EnableEvents = False
xInt = Target.Row
Me.Range("A" & xInt).Value = Date
Application.EnableEvents = True
End If
End If
End Sub
Reply
Report
0
0
Ryan
Ryan
This comment was minimized by the moderator on the site
i think a change is needed to line 7 as well...
Reply
0
0
iLadyT
iLadyT
This comment was minimized by the moderator on the site
Does anyone know if there is a way to auto-populate and lock to prevent inputter from changing the date? When I lock the cells and protect the document then the cells do not populate at all.
Reply
0
0
Ashish
Ashish
This comment was minimized by the moderator on the site
I do not want to enter date but a constant value in the cell, eg: a number or a text string. please suggest the code changes for the same.
Thanks
Reply
Report
0
0
crystal
crystal     Ashish
This comment was minimized by the moderator on the site
Hi,If you want to enter a number, please replace the text Date with the number directly in the code. For enter a text string, please replace the text Date in the code with "you text string" (the double quotes are included).
<div data-tag="code">Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updated by Extendoffice 2020/09/28
Dim xRg As Range, xCell As Range
On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("B:B")) Is Nothing) Then _
Target.Offset(0, -1) = "a text string" 'Or directly enter a number
Application.EnableEvents = False
Set xRg = Application.Intersect(Target.Dependents, Me.Range("B:B"))
If (Not xRg Is Nothing) Then
For Each xCell In xRg
xCell.Offset(0, -1) = "a text string" 'Or directly enter a number
Next
End If
Application.EnableEvents = True
End If
End Sub
Reply
Report
0
0
Vincent Sanchez
Vincent Sanchez
This comment was minimized by the moderator on the site
I'm inputting this code into my excel workbook and nothing is happening. Could anyone please help? Ideally, I would like when something is put into column A, time would be put into column B.
View Attachments (1 / 5)
Reply
Report
0
0
crystal
crystal     Vincent Sanchez
This comment was minimized by the moderator on the site
Hi chapo,Try the below code. Hope I can help.<div data-tag="code">Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updated by Extendoffice 2020/10/12
Dim xRg As Range, xCell As Range
On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("A:A")) Is Nothing) Then _
Target.Offset(0, 1) = Time
Application.EnableEvents = False
Set xRg = Application.Intersect(Target.Dependents, Me.Range("A:A"))
If (Not xRg Is Nothing) Then
For Each xCell In xRg
xCell.Offset(0, 1) = Time
Next
End If
Application.EnableEvents = True
End If
End Sub
Reply
Report
0
0
Lucas
Lucas
This comment was minimized by the moderator on the site
Hi, I am using your code as a reference. I want to ask if it is possible to have the following:1. Prevent duplicated date entries2. Have the 2 macro inputs at the same time : Target.Offset(0,-1), Target,Offset(0,1)3. Possible to auto insert an image to the cell?
Was trying to figure it out myself but i can't seem to find any resources online which can help me
Reply
0
0
Matt
Matt
This comment was minimized by the moderator on the site
Hello, this formula works great.  However, is there a way to set it that it only updates the cell in column A if it is empty?  
Reply
0
0
crystal
crystal     Matt
This comment was minimized by the moderator on the site
Hi Matt,Sorry, I don't quite understand what you mean. Can you try to be more specific about your question, or provide a screenshot of what you are trying to do?
Reply
Report
0
0
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations