How to insert the =now() function in a cell automatically when I write in another and hit enter

Question

I would like to know how to:

Automatically have the =now() function in the column B of Excel, only displayed in each cell (B1, B2, .. Bν) whenever I write something in column A in the respective cells (A1, A2, .. Aν).

Answer 1

NEW ANSWER

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(A1<>"",IF(B1="",NOW(),B1),"")

Explanation:

The first 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 A1. If B1 is empty it enters the current date and time otherwise it outputs the existing content (the original timestamp).

---

ORIGINAL ANSWER

You could try this in B1:

=IF(ISBLANK(A1),"",NOW())

Explanation:

ISBLANK is a logical test that tests if the target cell has any input. It returns either TRUE or FALSE. The 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.

---

References:

• https://support.office.com/en-us/article/IS-functions-0f2d7971-6019-40a0-a171-f2d869135665
• https://support.office.com/en-us/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
• http://www.howtoexcelatexcel.com/excel-tips-tricks/create-a-timestamp-in-excel-with-formulas/

Answer 2

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.