Forum Discussion

Joseph Huisman's avatar
Joseph Huisman
Copper Contributor
Oct 01, 2018

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...
excel
iamirenemae's avatar
iamirenemae
Copper Contributor
Dec 05, 2023

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 

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Dec 05, 2023

iamirenemae 

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:

  1. Press Alt + F11 to open the Visual Basic for Applications editor.
  2. In the editor, right-click on VBAProject (Your Workbook Name) in the left pane.
  3. Choose Insert -> Module to insert a new module.
  4. 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 Sub

5. 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.

  • YDN10's avatar
    YDN10
    Copper Contributor
    Dec 29, 2024

    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 Sub

    I used GPT to check and update it so hopefully it works as well as yours tomorrow... fingers crossed!

Resources