Forum Discussion
Automatically adding the date/time to a cell when another cell is updated
Hi Joseph,
There is no function in VBA called Today, the VBA function equivalent to the Worksheet function https://support.office.com/en-us/article/TODAY-function-5EB3078D-A82C-4736-8930-2F51A028FDD9 is https://docs.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/date-function.
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
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 SubThe 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- Hi Haytham Amairah,
I am using this vba snippet on a shared worksheet that I use with my helpdesk team. If I type information into a cell, the date appears. If one of my teammates who shares the document, types in that cell, the date field appears blank. How can I fix this? 
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.
