Excel now() shouldn't update existing timestam

问题:

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

回答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.

回答2:

I suppose you want to record the time of edit in a range of cells (say A9:C20 and D11:E12) if any of the cell in (A2:C2 or E3:F4) is edited.

You can do this:

Right-click the sheet tab and select 'View code' and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2:C2,E3:F4")) Is Nothing Then Exit Sub
Range("A9:C20,D11:E12").Value = Now()
Range("A9:C20,D11:E12").NumberFormat = "mm/dd/yyyy hh:mm:ss"
End Sub

The timestamp will not be autoupdated unless any of (A2:C2 or E3:F4) is edited again.

回答3:

File > Options > Formulas > Check Manual Calculations > Uncheck 'Recalculate workbook before saving'. If needed also check enable iterative and set max iterations to 1 as stated above.

回答4:

Just change the default iteration to 1, so it recalculates one time before it stops.

Go to this:

1. Office button
2. Excel Options
3. Formulas
4. Iteration