If you want to have a timestamp that does not recalculate you will need to use a technique called Circular Formulas. This basically allows a cell to perform a function based on its own content.
You need to enable this functionality by going to File > Options > Formulas then ticking the
Enable iterative calculation. Change the Number of iterations to
1. Press OK and save your sheet.
IF is similar to the one in the original answer. It checks if
A1 is blank.
<> is the logical operator meaning
not equal to. The second
IF checks itself and runs if content is entered into
B1 is empty it enters the current date and time otherwise it outputs the existing content (the original timestamp).
You could try this in B1:
ISBLANK is a logical test that tests if the target cell has any input. It returns either
IF is structured so that if the target cell is blank it will output an empty string. If there is any input entered into
A1 it will output the current time and date.
It should be noted that each time the sheet is recalculated the value outputted by
NOW() will change to the current time.
It’s not always wise to use VBA for everything, but this is a good candidate, especially if you want to track whenever a row was changed, and not just when it was first entered. Insert the following into the code for the sheet with the timestamp column:
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Dim intersection As Range ' Change A:A,C:E to whatever range you want to watch. Set intersection = Application.Intersect(Target, Me.Range("A:A,C:E")) If Not intersection Is Nothing Then Dim r As Range For Each r In intersection.Rows ' Change B1 to whichever column is the timestamp. r.EntireRow.Range("B1").Value = Now Next End If Application.EnableEvents = True End Sub
In this example, columns A, C, D, and E are watched for changes, and when changes do occur, the current date and time is inserted into column B of the same row.