Excel now() shouldn't update existing timestamp

Question

I have an excel formula =IF(NOT(ISBLANK(A2)), TEXT(NOW(), "mm/dd/yyyy HH:ss"),"")

My Problem is: Of course, everytime I open this sheet it updates the cell value associated with most recent timestamp. I do not want this timestamp to change if it already existed.

What I want is somehow: if A2 is not blank and wasn't updated recently then dont update the timestamp else update.

I am looking for an inline function if possible.

Thanks

Answer 1

You can do this with a VBA macro:

1. Open VBA editor (Tools > Macro > Visual Basic Editor)
2. On the left, right-click Sheet1 and select View Code
3. Copy the following into the editor:

---

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 And Target.Row = 2 Then
    If Target.Value = "" Then
      Cells(2, 2).Value = ""
    Else
      Cells(2, 2).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
    End If
  End If
End Sub

---

If you write something into cell A2, then a timestamp will be written into cell B2, and when you delete A2, B2 will also be deleted. The timestamp won't be automatically updated.