Record Time of Entry

%3CLINGO-SUB%20id%3D%22lingo-sub-1525271%22%20slang%3D%22en-US%22%3ERecord%20Time%20of%20Entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525271%22%20slang%3D%22en-US%22%3EIs%20there%20a%20way%20to%20record%20the%20tone%20of%20data%20entries%20in%20a%20cell%3F%3CBR%20%2F%3E%3CBR%20%2F%3ESo%2C%20I%20would%20like%20the%20cells%20in%20column%20A%20to%20record%20the%20time%2C%20as%20I%20enter%20data%20in%20the%20cells%20of%20column%20B.%3CBR%20%2F%3EIt%20seems%20possible%2C%20I%20just%20haven%E2%80%99t%20found%20out%20how.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20You%20for%20any%20advice!%3CBR%20%2F%3E-Kyle%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1525271%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525320%22%20slang%3D%22en-US%22%3ERe%3A%20Record%20Time%20of%20Entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525320%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F706232%22%20target%3D%22_blank%22%3E%40Kyle_P19%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere's%20a%20function%20called%20NOW()%20which%20will%20get%20the%20current%20date%20and%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20is%20that%20it%20updates%20every%20time%20the%20worksheet%20is%20recalculated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20use%20that%20function%20but%20keep%20the%20time%20shown%20static%20as%20you%20add%20new%20rows%2C%20you%20can%20use%20iterative%20calculation%20to%20allow%20a%20circular%20reference%20to%20keep%20the%20value%20in%20the%20cell%20if%20it's%20already%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGo%20to%20File%26gt%3BOptions%26gt%3BFormulas%20and%20enable%20iterative%20calculation%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22OwenPrice_0-1594861646157.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205693i764979B6A2410B17%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22OwenPrice_0-1594861646157.png%22%20alt%3D%22OwenPrice_0-1594861646157.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20in%20A2%2C%20you%20can%20put%20this%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(B2%26lt%3B%26gt%3B%22%22%2CIF(A2%26lt%3B%26gt%3B%22%22%2CA2%2CNOW())%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou'll%20see%20that%20this%20formula%20is%20checking%20if%20there's%20a%20value%20in%20B2.%20If%20there%20is%2C%20it's%20checking%20if%20there's%20already%20a%20value%20in%20A2%20(where%20the%20formula%20is).%20If%20there%20is%20already%20a%20value%2C%20it%20leaves%20it%20alone.%20If%20there%20isn't%20a%20value%2C%20it%20uses%20the%20NOW()%20function%20to%20get%20the%20current%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22timestamp.gif%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205697i242919B066985FDB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22timestamp.gif%22%20alt%3D%22timestamp.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20workbook%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525331%22%20slang%3D%22en-US%22%3ERe%3A%20Record%20Time%20of%20Entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525331%22%20slang%3D%22en-US%22%3EOne%20other%20option.%20You%20could%20right%20click%20on%20the%20worksheet%20tab%20and%20select%20view%20code.%20Then%20paste%20this%20code%20into%20the%20code%20window.%3CBR%20%2F%3E%3CBR%20%2F%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3EIf%20Target.Cells(1).Column%20%3D%202%20Then%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3ETarget.Columns(1).Offset(0%2C%20-1).Value%20%3D%20Now%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FLINGO-BODY%3E
New Contributor
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
2 Replies

@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:

OwenPrice_0-1594861646157.png

 

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.

 

timestamp.gif

 

Example workbook attached.

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