Automatically adding the date/time to a cell when another cell is updated

[Joseph Huisman]

I am trying to create a spreadsheet where when a specific cell is updated in anyway, the date/time stamp automatically updates in the cell right beneath the cell that was updated. I only need to have a one to one relationship with multiple cells. IOW, I have 5 cells in a row going across. When any one of the 5 cells is updated, I want an updated date/time stamp to appear in the cell directly below the cell that was updated. Is there a way to do this? 

[#2]

Hi Joseph,

This is possible, but you definitely need to a VBA code to do it!

Let's say the five cells you want to target are from cell A1 to E1, so please hover the Mouse over the Worksheet tab, right-click, and select View Code.

Then copy and paste this code into the worksheet code module:

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1")) Is Nothing Or _
    Not Intersect(Target, Range("B1")) Is Nothing Or _
    Not Intersect(Target, Range("C1")) Is Nothing Or _
    Not Intersect(Target, Range("D1")) Is Nothing Or _
    Not Intersect(Target, Range("E1")) Is Nothing Then

    Target.Offset(1, 0) = Now
 End If

End Sub

After that, save the workbook as .xlsm file extension to keep the code saved in it.

If you want to apply it to different cells, simply, you can change the cell ranges in the code and their order in the code does not matter!.

Hope that helps

[#3]

Haytham - Thank you so much for the quick response on this. This works perfectly and I can see that changing the target offset allows me to change the location of the timestamp. 

This is great and exactly what I needed. I really appreciate the help. 

[#4]

Thank you, but I have discovered a simplified version of the code as follows:

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1,B1,C1,D1,E1")) Is Nothing Then
    Target.Offset(1, 0) = Now
 End If

End Sub

It works the same as the previous one, so I recommend you to use it instead.

Regards

[#5]

Thanks for the simplified version. One more question for you, I tried replacing "Now" with "Today" to get just the date but that didn't work. How can I change this so it only shows the date if needed? 

[#6]

Hi Joseph,

There is no function in VBA called Today, the VBA function equivalent to the Worksheet function Today is Date.

So you have to use Date instead of Today as follows:

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1,B1,C1,D1,E1")) Is Nothing Then
    Target.Offset(1, 0) = Date
 End If

End Sub

After you apply this change, you may get the date in the worksheet like this:

Where time appears as zeros!

If you encounter it, don't worry, it's just a format, and you can change it to a short date from the Home tab as follow:

Hope that helps

[#7]

Thanks again. You have been a huge help here. So I have one more question on this. The macro works when I manually update the cell, the date plugs in just perfect and it updates every time I update the cell. However, I was hoping that this would also work if the cell was updated by input into another cell. 

For example, if A1 has data and A2 is where the date updates automatically, I want to have a formula in A1 that says "=Sheet2:A1". I then enter data into Sheet2:A1 and that data also updates in A1 on the original sheet but the date does not update in this case. 

What I am ultimately trying to do is create a sheet that updates the date when certain fields are updated but I want those fields to be updated by inputs from another sheet. I hope this makes sense. 

Is there a way to do this or does the macro only run if the cell is updated manually on that sheet? 

I appreciate any help you can provide. 

[#8]

Hi,

In this case, you should inject the Sheet2 with the same code but with some changes to move the time stamp to the right place in Sheet1!

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1")) Is Nothing Then
    Sheet1.Range("A2") = Now
 End If

End Sub

The above example for the Cell A1 in Sheet1 which has a formula referred to cell A1 in Sheet2 (=Sheet2!A1), and you need to put this code in the Sheet2 code module NOT in Sheet1.

If you have more than one cell referred to Sheet2 such as cell A1 and B1 in Sheet1, you have to duplicate the IF statement in the same code of Sheet2 to check each one separately:

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1")) Is Nothing Then
    Sheet1.Range("A2") = Now
 End If
 
  If Not Intersect(Target, Range("B1")) Is Nothing Then
    Sheet1.Range("B2") = Now
 End If

End Sub

[#9]

@Haytham Amairah  Hi  I too am looking to have the date updated in a cell when the designated cell is changed  I placed the VBA code in the sheet code tab but what do I do on the sheet itself? Any help is greatly appreciated thanks

[#10]

Hi @Deleted,

I think you need to change the cell references in the code to comply with existing data in your spreadsheet.

[#11]

@Haytham AmairahThanks Haytham, ill give that a try