Forum Discussion

Kyle_P19's avatar
Kyle_P19
Copper Contributor
Jul 16, 2020

Record Time of Entry

Is there a way to record the time of data entries in a cell?

So, I would like the cells in column A to record the time, as I enter data in the cells of column B.
It seems possible, I just haven’t found out how.

Thank You for any advice!
-Kyle
excel
Formulas and Functions

2 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Jul 16, 2020
    One other option. You could right click on the worksheet tab and select view code. Then paste this code into the code window.

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells(1).Column = 2 Then
    Application.EnableEvents = False
    Target.Columns(1).Offset(0, -1).Value = Now
    Application.EnableEvents = True
    End If
    End Sub
  • OwenPrice's avatar
    OwenPrice
    Iron Contributor
    Jul 16, 2020

    Kyle_P19 

     

    There's a function called NOW() which will get the current date and time.

     

    The issue is that it updates every time the worksheet is recalculated. 

     

    To use that function but keep the time shown static as you add new rows, you can use iterative calculation to allow a circular reference to keep the value in the cell if it's already there.

     

    Go to File>Options>Formulas and enable iterative calculation:

     

    Now, in A2, you can put this formula:

    =IF(B2<>"",IF(A2<>"",A2,NOW()),"")

     

    You'll see that this formula is checking if there's a value in B2. If there is, it's checking if there's already a value in A2 (where the formula is). If there is already a value, it leaves it alone. If there isn't a value, it uses the NOW() function to get the current time.

     

     

    Example workbook attached.

Resources