Auto Update Timestamp resets everytime opening the file

[shirazx3]

Hello,
I am trying to create an instance where a value entered in A1 gives a timestamp to in B1 and everytime A1 is updated, it updates the timestamp in B1. I was able to achieve it using macros but the issue I am facing is that everytime I open the file, all the timestamps are set to the time the file is opened.

Book1
	A	B
1	LOGIN DAY	LOGIN DATE/TIME
2	1	7/14/22 17:20
3	2	7/14/22 17:20
4	3	7/14/22 17:20
5	4	7/14/22 17:20
6
7
8
9
10
11
12
13
14
Sheet1

Cell Formulas
Range	Formula
B2:B14	B2	=MyTimestamp(A2)

Function MyTimestamp(Reference As Range)

If Reference.Value <> "" Then

MyTimestamp = Format(Now, "m/d/yy hh:mm")

Else

MyTimestamp = ""

End If

End Function

 

[davesexcel]

If you use a volatile formula, it changes automatically.
Since you are using VBA, you can use the worksheet_Change event.

Right click the sheet tab and select "View Code"
Paste this code there.
Enter something in A2:A10 and see what happens.Change target range as requiered

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once

    If Not Application.Intersect(Target, Me.Range("A2:A10")) Is Nothing Then    ' indicates the Target range
       Target.Offset(, 1) = Format(Now, "m/d/yy hh:mm")
    End If
End Sub

 

[shirazx3]

Thanks for the solution Dave.