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
kovesp's avatar
kovesp
Copper Contributor
Sep 04, 2021

No VBA needed; and that would not work when the workbook is opened in the browser or mobile platforms.

 

If the column where you want to automatically set the current date is say B, the column where the data is being entered is say C, and you are starting from B1 then therre are two variants:

  • If you want the date to update whenever the data is changed in column C, then place
       =IF(ISBLANK(C1)," ",NOW())
    in B1 and copy down.
  • If you want the date to be set only when the data in column C is changed from blank to a non-blank value (but not when the value is changed from non-black value to another non-blank value) then place
        =IF(ISBLANK(C1)," ",IF(ISBLANK(B1)," ",NOW()))
    in B1 and copy down.

These work when the workbook is opened from Windows or Android (so it probably also works on IOS, but I don't use that platform, so can't verify).

 

Note: There was a previous reply which was similar to the first option above, but used TODAY(). That won't work, because the TODAY() function updates whenever the workbook is opened. NOW() will not do that, it "freezes" the date when invoked.

nne1071's avatar
nne1071
Copper Contributor
Sep 20, 2022

kovesp 

many thanks for your formula, it works for me, just the problem is that if I update lets say C1, it does update the date/time into all lines & not only B1 as I was expecting. What is my mistake?

  • Ouche1195's avatar
    Ouche1195
    Copper Contributor
    Oct 29, 2022

    @kovesp

    Hi I have the same issue
    my spreadsheet has steps and when a step is changed or edited another cell should reflect the date
    and since i have many steps I need to see many dates to know when each step was edited

    for example:
    cells with content to be edited are :
    J8, J14, J23,P8,P14,P23

    and cells that should reflect changes date are:
    I11,I17,I26,O11,O17,O26

    So cell I11 should show the date when J8 is edited
    (whenever J8 is edited, I11 should update the date)
    and whenever J14 is edited, I17 should update the date (show the date),
    and so on

    I tried =IF(J8="","",NOW())
    and I tried =IF(J14="",IF(I17="",NOW(),I17),NOW())
    and tried =IF(ISBLANK(P14)," ",NOW())
    and tried =IF("$P8"="","",NOW())
    and =IF(P8<>"",IF(O11="",NOW(),O11),"")
    all of them are working but with 2 problems:
    - if i edit any cell in the sheet, the date is updated
    - but it is updated in all the cells that contains this formula despite that the formula in each one has different cell number as referred above


    any help is appreciated
    thank you in advance

    • NikolinoDE's avatar
      NikolinoDE
      Gold Contributor
      Oct 30, 2022

      Ouche1195 

      Beforehand, with your permission, I suggest you include a sample file (without sensitive data) or, if not possible, photos.

      Where you explain step by step what your plan is based on this.

      Knowledge of the Excel version, operating system and storage medium is a good prerequisite for getting a quick and more accurate solution.

      Don't forget to specify your region or language you use in Excel, as the formulas vary depending on the language.

      Please keep in mind that none of us know your digital environment, and therefore not what possible solutions could result from it.

       

      *It's always an advantage if you create a new discussion for your question.

       

      Thank you for your patience and understanding

       

      NikolinoDE

      I know I don't know anything (Socrates)

       

Resources