Forum Discussion

Joseph Huisman's avatar
Joseph Huisman
Copper Contributor
Sep 30, 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
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Apr 15, 2021

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)

Erin_OC_Direct's avatar
Erin_OC_Direct
Copper Contributor
Feb 06, 2024

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. 

  • Erin_OC_Direct's avatar
    Erin_OC_Direct
    Copper Contributor
    Feb 06, 2024
    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.
    • CharityT's avatar
      CharityT
      Copper Contributor
      Jan 08, 2026

      After the step of "Enable iterative calculation" in options, this formula didn't work for me since it seems backwards from what was being asked.

      This formula:
      =IF(A2<>"",IF(B2="",NOW(),B2),"")
      puts the time into B2 when data is entered into A2, if putting the time next to the data.

      To put the date below the data would require:
      =IF(D6<>"", IF(D7="", NOW(), D7), "")

      I also had to save the file as a macro-enabled excel file (.xlsm) for it to not change the date for all the cells that I copied the first formula into. Before I did that, if I copied the formula in B2 into cells B3:B200, if I put data into any of the cells A2:A200, it would change all the times in cells B2:B200 to the same current time. But once I saved the file as a .xlsm, the time entered in B2 based on data in A2 did not change when I entered data into A3 or A4, etc.

      • Lobo360's avatar
        Lobo360
        Brass Contributor
        Jan 13, 2026

        If you copy the formula from B2 that references A2 to B3 and beyond, Excel usually applies the same variation to the referenced cells so A2 becomes A3 when the host cell goes from B2 to B3 etc unless $ has been used to lock the reference explicitly. I’m not sure why yours need to be saved as an .xlsm but if it works for you then I guess roll with it. 

        Btw, on a similar thread I posted a similar solution that I felt was better as account for any changes to the target cell, not just the first change from null to something.  Linking here in case of interest:

        https://techcommunity.microsoft.com/discussions/excelgeneral/date-of-last-modified-for-a-cell/3690222/replies/4475694

Resources