logging the time a cell was edited

[PeteofEbor]

Hi. Is there a simple way to record the time that a cell was edited ? eg if I make an entry in cell A1, could I get cell A2 to hold and keep the time that A1 was edited ? I tried using now(), but that simply displays the current time each time the sheet recalculates.
Thanks.

[Lupp]

One action of editing can afflict many cells.
How to handle Del? How Ctrl+V…? What about Ctrl+Z? Would you have it tracked by time stamps as a specific action of editing? Or should contents be re-changed and the time-stamps together with them? How to handle formulas?
I’m afraid even a clear and reliable specification of what a timestamp feature is expected to do, is beyond our skills here.
Spreadsheets generally aren’t “history-aware”. If you want to change that you will have a laborous time - and the results may not provide the reliability you need if the time-stamps actually are relevant.

[PeteofEbor]

Thanks to everyone for the time and effort taken to provide answers… but I’m afraid to say that writing & using macros is a little above my skill level… certainly at present… Thanks anyway.

[Lupp]

Many years ago I tried to find ways to achieve what you want by standard formulas.
The method I found I named “Conditionally Suspended Self-Reference”. However, It was impossible to get it reliable and stable. I therefore since dissuaded from similar solutions, and ceased to publish the formulas anymore.

[anon73440385]

Hello

Is there a simple way to record the time that a cell was edited ?

Depends on what’s your understanding of “simple way”. You need

[1] - 2 macros
[2] - assign the macros to appropriate events

[1] The Macros

Global CurValOfA1

Sub OnSheetChanged()

  Dim vNewValOfA1
  vNewValOfA1 = ThisComponent.Sheets(0).getCellRangeByName("A1").Value
  
  If vNewValOfA1 <> CurValOfA1 Then
     CurValOfA1 = vNewValue
     ThisComponent.Sheets(0).getCellRangeByName("A2").Value = Now()
  End If
  
End Sub

Sub OnDocOpen
  CurValOfA1=ThisComponent.Sheets(0).getCellRangeByName("A1").Value
End Sub

[2] Macro Assignment

• Assign macro OnDocOpen to (document) event Open Document
• Assign macro OnSheetChanged to (sheet) event Content changed

See the following sample file: A2-Timestamp-Recording-on-Change-of-A1.ods

Remark(s)

• This does not log the edit (does not change the time in cell A2), if content of cell is not changed by the edit operation.
• This works only for numerical values in A1 (in that sense: your question needs more details, if a more sopisticated solution is required).

Hope that helps.