Logging TIme of Data Entry

Data Entry is a fact of life in any information system.  This is true whether the data is being entered straight into Excel or is being imported from another system which was the recipient of the data entry.

When the data was entered is often a worthy piece of information in itself.  If the data is being entered directly into Excel, there are several (some better than others…) ways of accomplishing this.  In our examples below, let’s assume we have the data being entered in Column C and we want the time it was entered in Column D:

1.    First of all, you could enter it manually by selecting the adjacent cell in Column D and pressing: Ctrl+Shift+;  This is a shortcut key for entering the current time.

2.    A better way to do this may, of course, be to use a Formula to enter the time. The =NOW() function not only records the current time, but the date as well.

3.    The problem with the approach described in #2 above is that this simple formula will Recalculate each time the worksheet is reopened or otherwise refreshed.  This is also the case (unfortunately) if you use a more sophisticated pre-filled formula such as =IF(C1="","",NOW()), since this will also update.

4.    If, therefore, you wish to take this to the next level, you may wish to try out some VBA code similar to the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("C1:C500")) Is Nothing Then
If Target.Count = 1 Then _
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
End If
Application.EnableEvents = True
End Sub

VBA is certainly not everyone’s cup of tea, but it is worth taking a look at occasionally.  Who know, you actually may find that you have hidden talents as a Code Expert!