CODEWITHSUNDEEP
excelexcel-formula
Vikram
Vikram

Reputation: 4190

Excel now() shouldn't update existing timestamp

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

Upvotes: 2

Views: 23364

Answers (5)

cjhoffmn
cjhoffmn

Reputation: 1

OK - so I realize this thread is ancient.
However, I ran across an excel formulaic approach to this that I thought I'd share:

Cell A1 = cell with Data Validation list of Complete,Incomplete Cell A2 = IF(A1="Complete",IF(A2="",NOW(),A2),"")

Iteration Needs to be turned on - so be thoughtful about other potential circular references - but this will return a blank cell until you flip A1 to "Complete." Once you've done that - it will update A2. A2 will then stay in that state as long as you don't change the status of A1.

You can also change the trigger to other fields etc, in my case, I keep it in incomplete until the form is ready, then flip it to Complete and the time stamp sticks.

Upvotes: -1

Bryan
Bryan

Reputation: 21

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.

Upvotes: 2

Kal_EL
Kal_EL

Reputation: 1

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

Upvotes: 0

kcdenley
kcdenley

Reputation: 46

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.

Upvotes: 2

kol
kol

Reputation: 28698

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.

Upvotes: 5

Related Questions