How to Auto Populate Date in Excel When Cell Is Updated

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. In this article, we will show you how to auto-populate date in Excel when a cell is updated. Here, we will use the Excel VBA to describe the process.

Download Practice Workbook

Download the practice workbook from here.

2 Useful Examples to Auto Populate Date in Excel When Cell Is Updated

Let’s say, we have a dataset (B4:C11) in Excel. Here, we will put some value in a cell of column B and then the adjacent column C cell will show the date and time of changing the cell. In this article, we will see two examples of using Excel VBA to auto populate the current date in Excel when a cell is updated. So, without further delay, let’s get started.

1. Apply Excel VBA to Auto Populate Dates in Entire Column When Cell Is Updated

VBA is a programming code that creates macros to perform simple or complicated Excel activities. In this method, we will apply Excel VBA to auto populate dates in the entire column B of the above dataset (B4:C11). We need the Developer tab to write VBA code. Follow the steps below to import the Developer tab to the ribbon and then apply the VBA code to auto populate dates.

Steps:

• First, check out whether your worksheet is showing the Developer tab in the tab list.
• However, we can not see the Developer tab in the tab list of the following image.

• To bring the Developer tab in the ribbon, first of all, go to the File tab.

• Secondly, choose Options from the left side menu.

• In turn, the Excel Options dialog box will appear.
• Eventually, click on Customize Ribbon located at the left side of the dialog box.

• Then, on the right side of the dialog box choose Main Tabs in the Customize the Ribbon dropdown.
• Next, in the Main Tabs section, you will see the Developer option is unchecked.
• Therefore, check the box.
• Click OK.
• See the screenshot for a better understanding.

• After that, we will see the Developer tab in the tab list.

• Now, to open the VBA editor, go to the Developer tab > find Code group > select Visual Basic (see screenshot).

• Hence, the Microsoft VBA code editor will open like the following image.
• Besides, we can also use the keyboard shortcut Alt + F11 to open this window.

• Afterward, to open the Sheet2 worksheet (named as Entire Column) related code editor, double-click on Sheet2 (Entire Column) on the left side of the editor.

• Thereupon, click the drop-down part of the (General) and choose Worksheet.
• When you select Worksheet, all worksheet related pre-built functions will be available to you.

• In the same way, from the right-side tab, choose the Change option.

• At this time, you will find that two codes are appearing in the code editor.

• Select the lower part code (see screenshot).

• Press the delete key on the keyboard.
• Consequently, the code editor will show the following two lines code shown in the picture below:

• Accordingly, insert the VBA code below between these two lines:

' Auto Date
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range("B:B").Column Then
If Cell.Value <> "" Then
Cells(Cell.Row, "C").Value = Now
Else
Cells(Cell.Row, "C").Value = ""
End If
End If
Next Cell

• We can see the final look of the VBA code in the image below.

• From the VBA code, we can see that it is applicable for the entire B column.
• Forthwith, click on the Save button located at the upper part of the code window.

• You will see the following Microsoft message (see screenshot).
• This message will show if your workbook is saved with .xlsx extension instead of .xlsm extension.
• As your workbook now has VBA code, you can not save it with the .xlsx extension.
• So, select No as we want to save the workbook as a macro-enabled file type.

• Momentarily, choose Excel Macro-Enabled Workbook option from the Save as type menu.

• Thus, click on the Save button to save the file newly.

• Return to the worksheet and type anything you want in any cell of column B.
• In our case, we typed John in cell B5.

• Press the Enter key and then you will get the current date & time in the adjacent cell C5.

• Check it out for the other cells of column B.

• Finally, we can see that the VBA code auto populates the current date & time in the entire column B (see screenshot).

2. Auto Populate Dates in Some Specific Cells While Updating with Excel VBA

This time we are going to show how to auto populate dates of some specific cells (B5:B8), not the entire column. Look at the following dataset (B5:B8). Say, in this worksheet, people can only update data on cell range B5:B8 and adjacent cells on column C will record the date of last changes. The process of this method is almost similar to method 1, the only difference is in the VBA code. See the following steps to apply the VBA code of this method.

Steps:

• To begin, go to the Developer tab > Code group > click on Visual Basic.

• As a result, the VBA code window will appear.
• Next, double-click on the worksheet named Sheet3 (Specific Cells).
• After that, the VBA code editor will look like the picture below.

• Eventually, select Worksheet from the dropdown of the (General) box.

• Then, choose Change from the dropdown options of SelectionChange (located at the right-side of the code window).

• At this time, select the second code in the code window (see screenshot).

• Therefore, delete the selected code.
• Eventually, keep your cursor in between the two lines code shown in the screenshot below:

• Later, insert the VBA code below between the two lines (see screenshot):

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("B5:B8"), .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

• The VBA code shows that we can only update the specific range B5:B8 in column B.
• Lastly, click on the Save button in the code window.

• To check the VBA code, enter any data (Merry) in cell B5.

• After pressing Enter, you will get the timestamp in the adjacent cell C5.

• Moreover, enter data in another cell (B7) in the B5:B8 range.
• Again, you will get the output in the adjacent cell (C7).

• But, if you enter data in a cell (B10) out of the range (B5:B8) specified in the VBA code, it will not return any date.
• We can see the blank cell (C10) adjacent to cell B10 in the screenshot below.

Conclusion

I hope this article will be helpful for you to auto populate dates in excel when the cell is updated. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.