Forum Discussion
Date of Last Modified for a Cell
Like this. Don't forget to enable Iterative Calculation, otherwise it won't work.
=IF(OR(J2:L2<>""), IF(M2<>"", M2, NOW()), "")
Thanks for your reply HansVogelaar.
This seems to only work if the modification is blank to non-blank? (Iterative Calculation is enabled)
Thank you! It worked for me, EXCEPT: it only works when cell changes from empty cell to X, Y, Z but it does not work if the content of the cell changes from Y to Z. Do you know a way around that?
As mentioned before in this discussion: the proposed formula will only create a timestamp when the cell it refers to was empty, but is now filled by the user. The timestamp will remain unchanged when the value is changed.
If you want the timestamp to be updated each time the cell is edited, it requires VBA code, which will work in the desktop version of Excel for Windows and Mac.
alternatively you could include the most recent value(s) into the timestamp cell something like this and then detect change accordingly:
=LET(in, A1:C1, ,me, D1, IF(AND(in=TAKE(TEXTSPLIT(me,";"),,-COLUMNS(in))),me,TEXTJOIN(";",0,TEXT(NOW(),"yyyy-mm-dd hh:mm"),in)))
If you want the date/time to change each time a cell in J2:L2 is edited, you do need VBA. A formula won't work,
