In this article, I will show you how to auto-populate date in Excel when a cell is updated. I will show the whole process using Excel VBA. I will also show how you can modify the code for your specific purposes.
Sometimes several people work on a worksheet and anyone can update the worksheet anytime. So, the worksheet owner might want to see the last updates with the update date and time.
Auto-populating dates in cells using Excel VBA (for the whole column)
Take a look at the following dynamic image. You see when I put some value in a cell of column A, the adjacent column B cell shows the date and time I am changing the cell.
Read More: How to Combine Date and Time in One Cell in Excel (4 Methods)
You update a cell and the adjacent cell shows the update date and time.
VBA Code and step by step procedure
Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range("A:A").Column Then
If Cell.Value <> "" Then
Cells(Cell.Row, "B").Value = Now
Else
Cells(Cell.Row, "B").Value = ""
End If
End If
Next Cell
End Sub
This is the VBA code that I am going to use. Let’s show you where to put this code.
1) Click on the Developer tab. And then click on the Visual Basic command in the Code window.
To open Excel VBA editor go to Developer tab and then click on the Visual Basic command in the Code window.
2) Microsoft VBA code editor will open like the following image. The keyboard shortcut to open this window is ALT + F11.
When you will open the VBA editor, it might show blank or the existing Macro codes already available in the same workbook or from any workbook open in your Windows system.
3) Double click on the worksheet on which you want to apply our code. So, you will find an editor that looks like this.
To open the Sheet1 worksheet (named as VBA) related code editor, double-click on the Sheet1 (VBA) on the left side of the editor.
4) Click the drop-down part of the (General) and choose Worksheet.
Select Worksheet from the drop down (on the left side). When you will select Worksheet, all worksheet related pre-built functions will be available to you.
5) In the same way, from the right-side tab, choose the Change option. You will find that two codes are appearing in the code editor. Select and delete the lower part code.
Delete the SelectionChange related code from the editor. It is not necessary. I am suggesting to delete this part just for making the editor clean for work.
6) Now insert the code below between these lines:
Private Sub Worksheet_Change(ByVal Target As Range)
‘ Insert code here
End Sub
' Auto Date
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range("A:A").Column Then
If Cell.Value <> "" Then
Cells(Cell.Row, "B").Value = Now
Else
Cells(Cell.Row, "B").Value = ""
End If
End If
Next Cell
When Worksheet is selected, Change is selected, input the above code in the function area.
7) We are done. Now save your workbook pressing CTRL + S or from the File tab. You will see the following Microsoft message. This message will show if your workbook is saved with .xlsx extension instead of .xlsm extension. Your workbook now has VBA code, so it cannot be saved with .xlsx extension.
Select No as we want to save the workbook as a macro-enabled file type.
You cannot save a workbook with macro codes with .xlsx extension.
8) Choose Excel Macro-Enabled Workbook (.xlsm) option from the Save as type menu and save the file newly.
Choose the Excel Macro-Enabled Workbook (.xlsm) from the options.
So, this is the whole procedure to auto-populate a cell when a change occurs in the adjacent cell.
Now the big question. How will you use this code for your specific purpose?
Say you want to use this code for another worksheet and for specific cell ranges. Like the image below.
You want to use this code for VBA2 worksheet and for the columns C and D.
We will now modify the above code for different cell ranges.
Here are the steps:
1) Open the Visual Basic editor and you might find that already the previous code is showing while the previous worksheet is selected.
Excel VBA editor will show the existing codes and related worksheet when you will open again the VBA editor.
2) Double click on the worksheet on which you want to apply your VBA code. On the right side, choose Worksheet on the left and on the right choose the Change option from the drop-down.
To select a code editor for a specific worksheet, you have to double-click on it.
3) Now use the following code:
' Auto Date
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range("C:C").Column Then
If Cell.Value <> "" Then
Cells(Cell.Row, "D").Value = Now
Else
Cells(Cell.Row, "D").Value = ""
End If
End If
Next Cell
I am showing the places where you will make the changes.
See where I am making the changes: C:C and D.
Auto-populating dates in some specific cells (not the entire column)
Look at the following spreadsheet. Say, in this worksheet, people can only update data on cell range D3: D22, and adjacent cells on column E will record the date of last changes.
This time we are going to show how to auto-populate dates of some specific cells (D3: E22), not the entire column.
1) Use the following code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D3:D22"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
Input this code and then see the magic.
2) Here is the result
Looks good.
This code will impact only the cell range D3: E22. Not any cell beyond this range.
If your developer tab is not showing
1) At first, check out whether your worksheet is showing the Developer tab in the tab list.
No developer tab is showing in the following image.
2) Click on the File tab.
3) Now choose Options from the left side menu.
4) Excel Options dialog box will appear. Choose Customize Ribbon and then on the right side of the dialog box, you see the Developer option is unchecked.
5) Check the box and click OK on the dialog box.
6) You see, now the Developer tab is showing in the tab list.
Download Working File
So, these are two pieces of code that you can use for your specific purposes. Do you know any better way to auto-populate dates in Excel when cells are updated? Let me know in the comment box.
I altered the code to produce different results: Putting the Date in column E and the Time in column F. I am just wondering if there’s a better or shorter way to get this same results?
My revision:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range(“D3:D22”), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = “dd mmm yyyy”
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
‘Second Function ****
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range(“D3:D22”), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.NumberFormat = “hh:mm:ss”
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
I will check out this code. Thanks for the input.
when we normally use as range object we also use set = “something here”. In the very first code above when we declare cell as range we did not use the set keyword. I did not understand why.Can you please explain it to me.
Private Sub Worksheet_Change(ByVal Target As Range)
‘ Auto Date
Dim Cell As Range
can I use 2 time stamps in the same excel sheet
input data in column “A” auto date in Column “C”
input data in column “K” auto date in Column “L”
code used:
Private Sub Worksheet_Change(ByVal Target As Range)
‘ Auto Date
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range(“D:D”).Column Then
If Cell.Value “” Then
Cells(Cell.Row, “B”).Value = Now
Else
Cells(Cell.Row, “B”).Value = “”
End If
End If
Next Cell
End Sub
End Sub
Hi Kawser.. cool solution.. like your code. Another way to solve this is with a worksheet formula as follows:
=IF(A2=””,””,IF(B2″”,B2,NOW()))
The above assumes your data will go in column A, starting with A2 and the date/time stamp will go in column B, starting with B2. For this to work, you must enable Iterative Calculation in the Options=>Formulas=>Calculations options dialog. Just copy the formula down column B for as far as you want the stamp to work. While VBA can be more flexible, the above is a good way to get it done with a cell formula and an option change. I hope you like it. Thanks again for all of your tutorials.. super helpful and interesting.. makes me better at EXCEL, every time you send them. Thumbs up!
Not sure what happened above to the formula. I think the editor can’t show the less than / greater than symbols. The formula should be:
=IF(A2=””,””,IF(B2 less than symbol and greater than symbol “”,B2,NOW()))
Hope that helps..
Is there a way to auto-populate a date based on a specific day rather than current day? For example auto-populate 60 days from August 28, 2019 rather than 60 days from current date?
Thanks for always posting valuable info. I don’t know VBA, but in Excel NOW is volatile. If you insert it as described above, is the date static?
Interested in this same function – but for ROWS vs. columns. We have a shared spreadsheet that needs to be updated by 20+ folks, and want to capture the date ANY TIME a field changes in the ROW. We would allocate column A to be the date field that would get updated if any cell in the row from B:AZ (or however big the range you want). Can’t seem to find this capability.
Hi,
Thanks for this code it is very close to what I am looking for. I need to change the code to capture the date when one of seven different fields is updated. I have changed the range to include all the columns, but it still only works for the first column. Does anyone know what I am doing wrong?
Thanks
Private Sub Worksheet_Change(ByVal Target As Range)
‘ Auto Date
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range(“J:J”).Column Then
If Cell.Value “” Then
Cells(Cell.Row, “Q”).Value = Now
Else
Cells(Cell.Row, “Q”).Value = “”
End If
End If
Next Cell
End Sub
Great solution perfectly explained Well done sir!
One mod I’m not seeing… I have a short column of cells (B20:B29), which, if one changes, they all change. albeit they may very well hold the same value. Adjacent cells values (C20:I29) will change.
Code works fine however, I only need one date cell currently in B18
Modifying your code in part to read:
If Not Intersect(Range(“B20”), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(-2, 0).ClearContents
Else
With .Offset(-2, 0)
changes the date in that cell, but only if attributed to B20 else it overwrites successively the values in B .Offset(-2, 0).
I’m not so clever in VB I can figure out a solution.
Any offers?
Hi Kawser,
Great code for udating the date.
One thing that I would like to see (how to do it) is to have the date change every time the adjacent cell changes.
Some 4500 rows of data and the status column (lets say N:N) can change between 5 different listings. I do use the Filter Function to generate a report, however, if the date is changed by VBA (my code) the #Spill will not occur in the report sheet. Adding the date manually allows the SPILL.
My code I have is:
Private Sub Worksheet_Change(ByVal Target As Range)
‘Auto Date input when adjacent cell changes.
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 14
xTimeColumn = 15
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
Just hoping someone may know the answer.
Cheers
Hi Greg,
That is really good input from you. Hope your piece of code will help people who are in trouble. Cheers!
Best regards
Thanks for this code!
When I run it the date isn’t input into the adjacent cell until I go back up to the cell.
Do you know any fix for this?
How do you add multiple codes for the same sheet? I want to auto-date multiple columns, not just 1.
Hi,
when im closing the file and open it again, the function is not working.
you know maybe why ?
i saved it as enabled workbook
The only thing I don’t like about this is that “undo” no longer works on the cells that are being monitored. Is there any way to change that?? Otherwise, this was very helpful, thank you. As info: For my specific needs, I wanted to check multiple columns, so I just repeated the first solution. Not at all elegant but it worked. Also, I didn’t want the date to get deleted if a cell changed back to empty, so I changed the ELSE result to also be = Now.
Like this:
Private Sub Worksheet_Change(ByVal Target As Range)
‘ Auto Date column L if any cell in columns B thru K are modified
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range(“B:B”).Column Then
If Cell.Value “” Then
Cells(Cell.Row, “L”).Value = Now
Else
Cells(Cell.Row, “L”).Value = Now
End If
End If
Next Cell
For Each Cell In Target
If Cell.Column = Range(“C:C”).Column Then
If Cell.Value “” Then
Cells(Cell.Row, “L”).Value = Now
Else
Cells(Cell.Row, “L”).Value = Now
End If
End If
Next Cell
For Each Cell In Target
If Cell.Column = Range(“D:D”).Column Then
If Cell.Value “” Then
Cells(Cell.Row, “L”).Value = Now
Else
Cells(Cell.Row, “L”).Value = Now
End If
End If
Next Cell
End Sub
Thanks a lot of your input, Andy!
Is there a way to implement this without the timestamp being included in the date? Not just formatting, just in the formula bar as well.
Good explanation. Thanks.
However, I should like to adapt this to get the dates in a row instead of in a column.
Would appreciate any suggestions. Thanks.
How can you edit this code to check 3 columns or more for data entry instead of the 1?