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?

  • 2
    This could be done with VBA and a worksheet change event. Jun 4, 2021 at 15:53
  • that'd be the only way I can think of
    – Alex M
    Jun 4, 2021 at 22:33
  • You may insert this one in DATE Column,, =If(Isblank(A1), "", Today()) ,,, but this will insert Current Date ,, later on you may manipulate the formula for Date, if need any specific Date !! Other is VBA Worksheet Change Event !! Jun 7, 2021 at 4:40
  • Please edit the post & add the method of formula you have applied so far,, since many of us have suggested how to fix it,,, also add VBA TAG if you need VBA based solution !! Jun 8, 2021 at 5:20

2 Answers 2


(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.

enter image description here

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())),"")

enter image description here


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:

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .