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

Copper Contributor

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? 

46 Replies

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

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. 

 

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

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? 

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!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:

Short Date.png

 

 

Hope that helps

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. 

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

 

@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

Hi @Deleted,

 

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

hi

can you please tell me how can i change from lines to collum? i'm trying to do the same thing but instead of changing specific rows, i'd like to everytime i change a collum cell it brings the now date on the right cell :(

@Haytham Amairah 

@Joseph Huisman 

 

With the permission of everyone, here is another solution without VBA.

Simply with a formula.

 

=IF(C1="","",TODAY()) in english

=WENN(C1="";"";HEUTE()) in German

 

Info in the inserted file.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

Please don't forget to change the date format of the cells
Hi there, I'm looking to have a column that updates whenever any cell in that row is updated. I'm just having trouble coming up with a formula. Could you help?

Thanks!!!

@Carly_Forrester 

I cannot follow your flow of thoughts.

Please attach a file (without sensitive data) and on the basis of this you explain your plan in detail.
It makes no sense that we are constantly producing files that are not what you imagine.

 

It is good to read this information in advance:

Welcome to your Excel discussion space!


Thank you for your patience and understanding

 

Nikolino

Hi, I tried using this code, and the first code mentioned. However, it doesn't seem to be working for me. regardless of what i enter in A1/B1/C1/D1/E1, there seems to be no output in A2/B2/C2/D2/E2.

Is there any settings that i should enable before writing this vba?

No VBA needed; and that would not work when the workbook is opened in the browser or mobile platforms.

 

If the column where you want to automatically set the current date is say B, the column where the data is being entered is say C, and you are starting from B1 then therre are two variants:

  • If you want the date to update whenever the data is changed in column C, then place
       =IF(ISBLANK(C1)," ",NOW())
    in B1 and copy down.
  • If you want the date to be set only when the data in column C is changed from blank to a non-blank value (but not when the value is changed from non-black value to another non-blank value) then place
        =IF(ISBLANK(C1)," ",IF(ISBLANK(B1)," ",NOW()))
    in B1 and copy down.

These work when the workbook is opened from Windows or Android (so it probably also works on IOS, but I don't use that platform, so can't verify).

 

Note: There was a previous reply which was similar to the first option above, but used TODAY(). That won't work, because the TODAY() function updates whenever the workbook is opened. NOW() will not do that, it "freezes" the date when invoked.

See my reply below, but use this instead of the first version:
=IF(COUNTA(C1:Z1)=0," ",NOW()).
Adjust for however many columns you want to cover in the row.

 

Sorry replied to @NikolinoDE instead of @Carly_Forrester.

I have a similar problem. I'm trying to update one cell in a row (column G) if any other cell in that same row is modified (columns range from A to R). So for example, in row 3, if cells A3:R3 are modified (number or text or dropdown menu change), then cell G3 updates to that day's date only (no timestamp). Any help is appreciated!