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.

auto populate date in excel when cell is updated


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.

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

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

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

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

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

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

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

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

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

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

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


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.

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

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

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

  • 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

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

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

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

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

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


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.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

34 Comments
  1. 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

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

    • Hi Anil,

      You are right. We normally use the “Set statement” to assign range objects. But it is done here indirectly without the Set statement.

      This is a Change event. Here, “Target” indicates to all cells within the sheet. So the “For Each Cell In Target” statement works as the alternative to the Set statement.

      Hope this clarifies your confusion. And thank you for reaching out to us.

      Regards
      Md. Shamim Reza (ExcelDemy Team)

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

    • You can check the following code for that. Just copy the ElseIf statement for more columns.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Cell As Range
      For Each Cell In Target
      If Cell.Column = Range("A:A").Column Then
      If Cell.Value <> "" Then
      Cells(Cell.Row, "C").Value = Now
      Else
      Cells(Cell.Row, "C").Value = ""
      End If
      ElseIf Cell.Column = Range("K:K").Column Then
      If Cell.Value <> "" Then
      Cells(Cell.Row, "L").Value = Now
      Else
      Cells(Cell.Row, "L").Value = ""
      End If
      End If
      Next Cell
      End Sub

      Thanks for reaching out to us. Keep in touch.

      Regards
      Md. Shamim Reza (ExcelDemy Team)

  4. 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!

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

    • Hello, WAYNE EDMONDSON!
      Thanks for sharing your thoughts with us!
      Stay Tuned!

      Regards,
      Sabrina Ayon
      Author, ExcelDemy.

  6. 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?

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

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

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

  10. 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?

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

  12. 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?

  13. How do you add multiple codes for the same sheet? I want to auto-date multiple columns, not just 1.

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

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

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

    • Reply
      Osman Goni Ridwan Sep 1, 2022 at 12:49 PM

      Hello ABBY!
      You have to change a line in the VBA code that is defining the format of the output.

      Insert this line:
      .NumberFormat = “dd mmm yyyy” instead of this .NumberFormat = “dd mmm yyyy hh:mm:ss”

      So, the code will become as follows-

      Private Sub Worksheet_Change(ByVal Target As 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
      End Sub
  17. 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.

    • Reply
      Osman Goni Ridwan Sep 1, 2022 at 1:46 PM

      Hello ARIF, to get the dates in the next row use the following code when you will insert values in cells of range B3:I3 and want to auto-populate date and time in cell range B4:I4.

      CODE:

      Private Sub Worksheet_Change(ByVal Target As Range)
      With Target
                  If .Count > 1 Then Exit Sub
                  If Not Intersect(Range("B3:I3"), .Cells) Is Nothing Then
                      Application.EnableEvents = False
                      If IsEmpty(.Value) Then
                          .Offset(1, 0).ClearContents
                      Else
                          With .Offset(1, 0)
                              .NumberFormat = "dd mmm yyyy hh:mm:ss"
                              .Value = Now
                          End With
                      End If
                      Application.EnableEvents = True
                  End If
              End With
      End Sub

      You can change the cell range as your want. I hope, your problem will be solved in this way. If not, please share the Excel file and send us the problem with a little more explanation in an email at [email protected]

  18. How can you edit this code to check 3 columns or more for data entry instead of the 1?

    • Hi

      You can check the following code for that. Just copy the ElseIf statement for more columns.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Cell As Range
      For Each Cell In Target
          If Cell.Column = Range("A:A").Column Then
              If Cell.Value <> "" Then
                  Cells(Cell.Row, "M").Value = Now
              Else
                  Cells(Cell.Row, "M").Value = ""
              End If
          ElseIf Cell.Column = Range("B:B").Column Then
              If Cell.Value <> "" Then
                  Cells(Cell.Row, "M").Value = Now
              Else
                  Cells(Cell.Row, "M").Value = ""
              End If
          ElseIf Cell.Column = Range("C:C").Column Then
              If Cell.Value <> "" Then
                  Cells(Cell.Row, "M").Value = Now
              Else
                  Cells(Cell.Row, "M").Value = ""
              End If
          End If
      Next Cell
      End Sub

      Thanks for reaching out to us. Keep in touch.

      Regards
      Md. Shamim Reza (ExcelDemy Team)

  19. Hello,

    I am following the auto date. I am just wondering how do I do it if I would like another column to act the same.

    For example, I fill out Column C with data, and Column B will automatically update with the date when Column C was filled out.

    How will I do it if I would like to have Column E to automatically update with the date and time when I fill Column D with “Delivered”

    I hope it does make sense. I tried following some pointers here and putting two and two together. But I can’t seem to make it work. Thanks so much!

    • Hello, GIA!
      As you mentioned, you fill out Column C with data, and Column B will automatically update with the date when Column C was filled out. All you need to do is change the range in your code, and also change the reference argument which is the offset. Try this code below.

      Private Sub Worksheet_Change(ByVal Target As Range)
      With Target
              If .Count > 1 Then Exit Sub
              If Not Intersect(Range("C5:C8"), .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

      Also, you can use the same code for column E to automatically update with the date and time when you fill Column D with “Delivered”. You just have to change the range.

      Private Sub Worksheet_Change(ByVal Target As Range)
      With Target
              If .Count > 1 Then Exit Sub
              If Not Intersect(Range("D5:D8"), .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

      Please follow the instructions of the method I linked down.

      https://www.exceldemy.com/auto-populate-date-in-excel-when-cell-is-updated/#2_Auto_Populate_Dates_in_Some_Specific_Cells_While_Updating_with_Excel_VBA

      Hope this will help you!
      Best Regards.

  20. I am using this code for auto dating, I am trying to add a way to lock down the row so no changes can be made after they sign and it provides the date.
    Dim Cell As Range
    For Each Cell In Target
    If Cell.Column = Range(“G5:G300”).Column Then
    If Cell.Value “” Then
    Cells(Cell.Row, “E”).Value = Now
    Else
    Cells(Cell.Row, “E”).Value = “”
    End If
    End If
    Next Cell

  21. I am trying to add coding that will lock the row after the date auto updates. Then I want it to create an audit log onto another sheet.
    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

    • Hello, JOHN!
      Thanks for your comment!
      You can lock the row after the date auto updates with the following code.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Address = "$A$1" And Target.Value <> "" Then
          ActiveSheet.Protect Contents:=False
          Range(Target.Address).Locked = True
          ActiveSheet.Protect Contents:=True
      End If
      End Sub

      A cell should only be locked if cell A1 was updated and it is not blank, according to this formula: if Target.Address = “$A$1” and Target.Value > “”
      Just substitute the relevant cell value for $A$1 to make the macro function on cell B1, cell D15, or any other cell. For this to function, the column and row references must be preceded by dollar signs.
      By changing > “” in the line above to = “desired value,” you may additionally lock the cell only if a certain value was entered, allowing you to do things like lock the cell only if OK was entered or anything similar.

      Hope this will help you!
      Good Luck!

      Regards,
      Sabrina Ayon
      Author, ExcelDemy
      .

Leave a reply

ExcelDemy
Logo