Excel.Tips.Net ExcelTips (Menu Interface)

Recording a Data Entry Time

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Recording a Data Entry Time.

Vinay uses an Excel worksheet for data entry. Information is entered in column A and Vinay would like to have a way to automatically add a time into column B, adjacent to the value entered in column A, that indicates when the value was entered.

There are several different ways you can accomplish this task. The first is to manually enter a time by selecting the adjacent cell in column B and pressing Ctrl+Shift+; (that's the semicolon). This shortcut enters the current time in the cell. The problem with this approach, of course, is that it isn't automatic and it takes some extra movement and keystrokes to implement.

A better approach would be to use a formula to enter the time. The NOW function returns the current date and time, and you can use it in a cell in this manner:


Of course, this simple formula is updated every time the worksheet recalculates. That means that the function returns the current time every time you enter a value in column A. This is undesirable because you don't want previous times to update. You could try to use a formula to check to see if something is in column A, as in this manner:


The problem is that a formula like this introduces a circular reference into the worksheet, which presents a whole host of challenges to work with. A better approach is to create a macro that automatically runs every time something is entered in column A. Right-click on the tab of the worksheet used for data entry and choose View Code from the Context menu. You'll see the Code window for the worksheet in the Visual Basic Editor, and then enter this into the window:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range
    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("A:A"))
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(0, 1)
                    .Value = Now
                    .NumberFormat = "hh:mm:ss"
                End With
                rCell.Offset(0, 1).Clear
            End If
    End If

    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
    MsgBox Err.Description
    Resume ExitHandler
End Sub

With the macro in place, anytime you enter something into a cell in column A, the adjacent cell in column B will contain the date and time (formatted to show only the time). If you delete something in column A, then the adjacent cell in column B is cleared, as well.

Note, as well, that you can change the setting for the NumberFormat property to reflect how you want the date to appear. As an example, you could change to a twelve-hour time format by changing "hh:mm:ss" to "h:mm:ss AM/PM".

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3116) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Recording a Data Entry Time.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!


Comments for this tip:

Franco Medina    30 Jan 2015, 17:15
how can it set it so that when Cell A instead of just writing anything you need to write YES and then it will do the time stamp.
Willy Vanhaelen    23 Jan 2015, 11:39

Just replace
.NumberFormat = "hh:mm:ss"
.NumberFormat = "dd/mm/yy hh:mm:ss"

You can adjust "dd/mm/yy hh:mm:ss" to whatever you need.
SRS    22 Jan 2015, 11:13
How can I adjust the macro to format to show date adn time?
neale    20 Jan 2015, 03:37
Thank you Allen, much appreciated
George    15 Jan 2015, 02:52
I have an excel file on location on a Windows 2008 server. If a user modifies something in the excel (enter data, delete data etc), can I know which user made changes in the excel, and which changes?
Vijay    09 Jan 2015, 04:59
I have formatted the time displaying cell (Cell "B") with blue color. If i delete the data in colum "A", then color will be erased.
Please suggest me any solution for this issue.
Vijay    08 Jan 2015, 03:16

  Its very useful to me. Thank you so much.
Pan Tom    08 Nov 2014, 02:42
Hello there,
I'm trying to keep a set of records containing date but i want to create a macro that will copy the data from a sheet to another where i will keep day by day records. All the data entry should be done to the first sheet and will be erased after copying to the second.
Thankx in advance
Michael (Micky) Avidan    03 Nov 2014, 11:19
@John Powell
Try the following codes:
Public TempVal

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column < 141 Then TempVal = Target
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column > 140 Then Exit Sub
    If Target <> TempVal Then Cells(Target.Row, 141) = Now
End Sub
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
dockhem    02 Nov 2014, 08:41
Can we do this thing not in other column but in insert comment of the same cell. Or can we put this in watermark over the cell ?
Dipen Mandalia    03 Oct 2014, 06:51
This is fantastic work like magic! love it!
John Powell    29 Sep 2014, 11:31
Is anyone developing a variant of Micky's formula to monitor several columns and update a single column with a date stamp?
I have 140 columns to monitor and column 141 should be the date stamp if any of them are edited.

If anyone has the modification and is willing to share - that would be great!!
JohnT    23 Sep 2014, 17:07
Hi, this macro works well however I am running into the same issue as Aluis above. This macro clears the "Undo" function which is a big issue for me as well.
I wanted to implement this into a shared tracking spreadsheet however if anyone accidentally types over a cell there is no way to Undo.

Sumit Bansal    28 Aug 2014, 09:17
Here is another way to enter date/time automatically that would not update on recalculation. It only uses formula and named range (not circular reference)

Evan    23 Jul 2014, 08:56
The following Macro worked great for me. When anything is changed in column A, then the date of the change will be displayed in the same row as A but in column O.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:A")) Is Nothing Then _

    Target.Offset(0, 14).Value = Now

End If
End Sub
aluis    07 Jul 2014, 14:40
how is this different?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E10:AM2000")) Is Nothing Then
        Target(1, 37) = Date
        Target(1, 37).EntireColumn.AutoFit
  End Sub
Aluis    07 Jul 2014, 14:35
Won't this macro clear my undo stack overflow in excel everytime it runs? If so then this is a huge problem for me.
Michael (Micky) Avidan    28 Jun 2014, 05:28
Some Corrections+Clarifications:
1) The 'Public TempVal' declaration belongs to the macros.
2 Both Macros should be entered in the Sheets Module.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)

Michael (Micky) Avidan    28 Jun 2014, 05:25
To whom it may concern.
The proposed Macro is not only too large - it also has a BIG(!) disadvantage because it will change the Date/Time whenever the user, accidentally, presses F2 as to edit a cell, in Column A, and immediately presses Enter (which leaves the cells value unchanged).
The following 2 macros provides the same Date/Time stamp BUT(!) prevents it from being updated in the case mentioned above.Public TempVal
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Then TempVal = Target
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    If Target <> TempVal Then Target.Offset(0, 1) = Now
End Sub
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)

Pradeep Bharadwaj    27 Jun 2014, 09:31
If the Column A value is getting changed per formula, will the above code works?

I tried in the worksheet and if enter the value in Column A manually, the code is working. If the value in Column A is derived from formula, this code is not working. Please help.
PhilP    10 Apr 2014, 12:48
in the line of code "Set rChange = Intersect(Target, Range("A:A"))" add the additional columns after A:A eg. Range("A:A,C:C,E:E")
R    09 Apr 2014, 16:38

This code works great. How would you set this to also work on changes in cells C, E, G, I?

Akshay Kulkarni    24 Mar 2014, 04:27
This tip is wonderful and saves me a painful exercise of auditing the entries inputed by other people.

Many thanks.


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 4+5 (To prevent automated submissions and spam.)
          Commenting Terms

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2015 Sharon Parq Associates, Inc.