Forum Discussion
Automatically adding the date/time to a cell when another cell is updated
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?
48 Replies
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!
Try this formula "=IF(A1-O1="","",NOW())"
I placed it in Cell P1 of the document I have it in. So when I make any changes in Cells A1 through O1, Cell P1 will change with the date and time the change was made.
I'm not sure how well it will work if you place it in a middle cell of the formula. I hope this helps.
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.
- 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!!!
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 SubAfter 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 Amairah I want to be able to type a date in one cell and have it appear in another cell. It just two cells involved. How can I do this please?
Let's say you want to type a date in A2 on Sheet1 and you want it to appear in D3 on another sheet.
Format D3 the way you want, and enter the following formula in that cell:
=IF(Sheet1!A2="", "", Sheet1!A2)
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 SubIt works the same as the previous one, so I recommend you to use it instead.
Regards
