How to create a column in Excel that tracks when data was input in a neighboring cell?

Question

I have some columns in an Excel spreadsheet like this:

Name      Date       Time

I would like for when users add a name to the first column (column A), that the Date and Time column automatically adds the date and time when the name was written in. This keeps a record of when an entry was made in column A.

Is there a way to have this data automatically filled in?

Answer 1

(This answer is based on the article How to Quickly Insert Date and Timestamp in Excel, where you will find more information with screenshots.)

With Excel formulas, it's next to impossible to automatically insert a timestamp that wouldn't change when the worksheet is recalculated. However, there exists a workaround that uses formulas while misusing one formula-calculation feature of Excel.

This feature to be misused is Circular Reference, which is when a series of calculations is written in a never-ending way. For example, setting cell A2=A1+A2 will lead to an ever-increasing value for cell A2, as long as the value continues to be recalculated.

Step 1 : Enable limited iterative calculation

• Open Excel File > Options > Formulas
• Under "Calculated options", enable "Enable iterative calculation" and set the maximum to a low value (1 is fine)

Now Excel will recalculate circular references, but will stop after a certain number of calculations.

Step 2 : Enter the cyclic formula

Suppose that the value is to be entered in the A column, starting from A2 and the Time column is in column B.

The formula will be : =IF(A2<>"",IF(B2<>"",B2,NOW()),"")

When entering a value in column A, the timestamp would automatically appear in the adjacent column B cell, and will not update when later modifying the A2 cell.

Alternative for modification date

The above method works for the setting column B2 to the time that the first value was entered in A2.

A variant formula is for the case when the time is to be updated every time that column A2 is modified.

Use this formula: =IF(A2<>"",IF(AND(B2<>"",CELL("address")=ADDRESS(ROW(A2),COLUMN(A2))),NOW(),IF(CELL("address")<>ADDRESS(ROW(A2),COLUMN(A2)),B2,NOW())),"")

Answer 2

You can do this using VBA as follows:

Place this code in the sheet code for the sheet you are working on:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRnge As String, MyTime As String, mydate As String
    MyRnge = Range("B2:B" & Rows.Count).Address
    If Not Intersect(Target, Range(MyRnge)) Is Nothing Then
        mydate = Format(Date, "Long Date")
        MyTime = Format(Now, "hh:mm:ss")
        Target.Offset(0, 1).Value = mydate & " " & MyTime
    End If
End Sub

Change MyRnge to the range you want to cover. Change formats for date and time to suit Change the offset from 1 to reflect the column you want the date/time to appear. Type the event in the selected column: