Forum Discussion
Automatically adding the date/time to a cell when another cell is updated
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)
NikolinoDE This works, but unfortunately it then changes the date and time for all the cells whenever the date and time changes. So it doesn't exactly work as an accurate way to show that a cell was filled on THIS DATE and THIS TIME, because, it continues to update.
See, in the image below, I entered text in C3 at 9:13, but as I added text to successive rows at 9:15, all the times changed to 9:15.
I am glad to have had this practice, though, so thank you for the tip. I am wondering if you or anyone else has a way to do what I am looking for in this "easy" way.
- I found a solution on YouTube!
https://www.youtube.com/watch?v=YEyggg2z04c
Steps:
File> Options > Formulas [tab] > check "Enable iterative calculation" then change Maximum Iterations to "1"
Then use this formula for the cell where you want the time or date stamp:
=IF(C5<>"", IF(B5="", NOW(), B5), "")
* C5 would be the cell in which the date/time will appear, B5 represents the referenced cell. So in this example, when data is entered into B5, the time/date of entry will appear in C5.
it doesnt work becuase if you will open the file the next day, the cell will show the present date even though you updated it yesterday. NikolinoDE
If you want the timestamp to be static and not update every day, you can use a combination of VBA (Visual Basic for Applications) and a worksheet event. The VBA code will be triggered whenever the specified cells are changed, and it will insert a timestamp in the cell below the updated cell.
Here's a step-by-step guide:
- Press Alt + F11 to open the Visual Basic for Applications editor.
- In the editor, right-click on VBAProject (Your Workbook Name) in the left pane.
- Choose Insert -> Module to insert a new module.
- Copy and paste the following VBA code into the module:
Vba code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your sheet name Dim updatedCell As Range Set updatedCell = Intersect(Target, ws.Range("A1:E1")) ' Adjust the range as needed If Not updatedCell Is Nothing Then Application.EnableEvents = False updatedCell.Offset(1, 0).Value = Now Application.EnableEvents = True End If End Sub5. Close the VBA editor.
Make sure to replace "Sheet1" with the actual name of your sheet, and adjust the range in the Intersect function to match the range of cells you want to monitor.
Now, whenever any cell in the specified range is updated, the cell below it will be populated with the current date and time. The timestamp will not automatically update daily; it will remain static once entered.
This sort of worked for me, however these are some changes I made to make this work:
I changed "Now" to "Date" to give me the date of the change.
I did not create a module, instead I right clicked the sheet of interest in the VBA code and clicked "view code" - which is where I subbed in the code you shared.
The updated code I used (specific to my excel):Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Partners") ' Ensure this matches your sheet name. ' Check if the change occurred in Column F If Not Intersect(Target, ws.Columns("F")) Is Nothing Then Application.EnableEvents = False ' Disable events to avoid recursion Dim cell As Range For Each cell In Intersect(Target, ws.Columns("F")) ' Update the corresponding cell in Column G with the current date ws.Cells(cell.Row, "G").Value = Date Next cell Application.EnableEvents = True ' Re-enable events End If End SubI used GPT to check and update it so hopefully it works as well as yours tomorrow... fingers crossed!
- 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!!!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:
Thank you for your patience and understandingNikolino
