Current Date and time per cell

This is a classic Excel requirement, and you’ve already discovered the key limitation:

NOW() and TODAY() are volatile → they will always recalculate.

To freeze date/time when a status changes, you must use VBA. There is no formula-only solution that is reliable.

Below is the correct, minimal, production-safe VBA pattern for exactly your case.

Paste this code into the worksheet module

Double-click the sheet name (not a standard module).

VBA Code (FIFO-safe, fast, robust)

Private Sub Worksheet_Change(ByVal Target As Range)

    ' Watch column I only
    If Intersect(Target, Me.Columns("I")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then Exit Sub

    Application.EnableEvents = False

    ' If status becomes Closed, stamp date/time
    If LCase(Target.Value) = "closed" Then
        If Me.Cells(Target.Row, "J").Value = "" Then
            Me.Cells(Target.Row, "J").Value = Now
        End If

    ' Optional: clear timestamp if reopened
    ElseIf LCase(Target.Value) = "open" Then
        Me.Cells(Target.Row, "J").ClearContents
    End If

    Application.EnableEvents = True

End Sub

Result

No refreshing
No recalculation
Audit-safe
Works forever

You want to keep timestamp even if reopened?

Remove this block:

ElseIf LCase(Target.Value) = "open" Then

    Me.Cells(Target.Row, "J").ClearContents

End If

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

You need VBA coding to stay date. NOW() function will change the date/time once the cell is recalculated.

You require a logic test that applies the Now() stamp when the criteria is met - a fresh change to “Closed” in the monitored cell - but when not met leaves the check cell as it was.

Solution:

=if(AND(i9=“Closed”,Cell(“address”,i9)=Cell(“address”)),Now(),j9)

Based on:

Date of Last Modified for a Cell | Microsoft Community Hub

This requires iterative calculation setting to be turned on, details of which can be found in the thread linked above. 

I have assumed that the cell update test { Cell(“address”,i9)=Cell(“address”) } will work for a dynamically updated rather than user updated cell - this needs to be tested.