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:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!


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

Comments for this tip:

Linda    13 Nov 2015, 12:03

You are a life saver! I did figure out how to save it last night, you'd think that those error boxes would give more direction for the technically challenged persons :)

This works perfect now and I THANK YOU for your help!!!!!
Willy Vanhaelen    13 Nov 2015, 10:07

1) A workbook with the extension .xlsx cannot contain macros. To save your workbook as a macro-enabled file select: Save As. In the dialog, click on the "Save as type:" box and select Macro-Enabled Workbook (*.xlsm)

2) This version of the macro will exclude the first 19 rows:

Private Sub Worksheet_Change(ByVal Target As Range)
   With Target
      If .Column <> 1 Or .Columns.Count > 1 Or .Row < 20 Then Exit Sub
      If .Cells(1).Offset(0, 2) = "" Then .Offset(0, 2) = Now
   End With
End Sub
Linda    12 Nov 2015, 13:18

I guess I spoke too soon :( for some reason it won't save. It works perfectly when I enter it but I can't figure out how to save it correctly. I wish I could include a picture here of what it does, but when I click 'save' it says:
"The following features cannot be saved in macro free workbooks:
-VB project

To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.

To continue saving as a macro-free workbook, click Yes."

On the left, below the task bar at the top, it says:

Project - VBAProject
-VBAProject (job 368.xlsx)
  -Microsoft Excel Objects
    -Sheet1 (Sheet1)

***First problem: I can't figure out how to "choose a macro-enabled file in the File Type List"! I don't know where to look for "file type list" or what to do???

***Second problem: I need the macro you gave me to be for Column C - BUT NOT START UNTIL ROW 20 - can that be done?

I'm sorry to be such a pain, but macros and VBA projects are out of my comfort zone and I'm just lost. Thank you for any help you can give.
Linda    12 Nov 2015, 12:15

PERFECT!!! Thank you soooooo much this works just the way I need it to. So grateful for you taking the time to answer me - made my job easier for sure! Really appreciate it!

YAY! :)
Willy Vanhaelen    10 Nov 2015, 11:35

This little macro will do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If .Column <> 1 Or .Columns.Count > 1 Then Exit Sub
   If .Cells(1).Offset(0, 2) = "" Then .Offset(0, 2) = Now
End With
End Sub

When you enter data in a cell in column A, the macro looks if the corresponding cell in column C is empty. Only if it is, it puts the date there.

Remeber that the macro must reside in the worskheet's code page: right click the sheet tab and select 'View Code'. That's the place to be.

Format column C to the date format of your liking.
Linda    09 Nov 2015, 10:43
Hey @Allen Wyatt OR @Willy Vanhaelen can either of you help with my post on Oct. 30 2015??

Or anyone, for that matter! Really need help on this, I just can't figure it out!!

Thanks :)
*yes I've read all of the comments, but I'm still lost on this one :(
Willy Vanhaelen    07 Nov 2015, 08:47

You should read all the comments :-)

Wayne asked the same question on 9 May 2015 and I proposed a solution on 10 May 2015.
Michele    06 Nov 2015, 11:04
This works great... what if I needed it for more than one column, like A with the time in B, C with the time in D, etc.?
Linda    30 Oct 2015, 12:20
This is a great site with GREAT info but I can't figure out how to skip a column for the date.
Example: I enter info into column A, and then I enter the price in column B. I need the date I do this to populate into column C.
I do NOT want the date to change if column A is edited.
The date in column C does NOT need to recognize column B info at all.
  A B C
Home Depot $25.00 TODAY'S DATE

ANY help would be so appreciated! Please include the whole formula so I can copy/paste (because I won't know how to insert just a portion of the function) :)
Isabela    29 Oct 2015, 15:11
GREATT!! Fabulous! after two days digging here is the solution!!! thanks :D
Chris     09 Oct 2015, 06:18
This is fantastic but does anyone know if its possible to have a time and date in for instance column A and then a further time and date in column O? I have tried adding the same values but it wont work. Any help or advice is greatly appreciated.
Steph    15 Sep 2015, 14:33
Is it possible to add to the original code you posted so we can also see the user's name that updated the information in the cell.
This post is very helpful. Thank you!
Lisa    14 Aug 2015, 13:55
This macro is very helpful, I'm new to Macros I need the time stamp in A column and not in B I'm trying several things to reverse it but nothing is working for me. I also need the time stamp of when I start typing in B column and not when I hit enter. Is there a macro command that can do that?
Willy Vanhaelen    15 Jul 2015, 06:21

.Offset(0, -1).HorizontalAlignment = xlLeft
HunterC    14 Jul 2015, 15:30
I would like to change the alignment to justify left. Can anyone help me with this in the code?

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If .Column <> 3 Or .Columns.Count > 1 Then Exit Sub
   If .Cells(1) > "" Then
      .Offset(0, -1) = Now
      .Offset(0, -1).NumberFormat = "mm/dd/yy hh:mm AM/PM"

      .Offset(0, -1).Clear
   End If
End With
End Sub
Akshay sharma    13 Jul 2015, 10:24
i want the cell A1 to be copied and pasted in the next cell A2....so on every minute...
Bharath    08 Jun 2015, 08:05

Thanks Willy it worked :)
Willy Vanhaelen    07 Jun 2015, 11:59

These macros must reside in the worksheet's code page. Right click the worksheet tab and select View Code. That's the place to be.
Bharath    06 Jun 2015, 06:44
For some reason this macro is not working for me :( I'm very much new to macros, please tell me if there's any prior setting to be made... Please help
Willy Vanhaelen    10 May 2015, 06:11
@ Wayne
This macro should do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If .Column <> 1 And .Column <> 3 Or .Columns.Count > 1 Then Exit Sub
   If .Cells(1) > "" Then
      .Offset(0, 1) = Now
      .Offset(0, 1).NumberFormat = "dd/mm/yy hh:mm:ss"
      .Offset(0, 1).Clear
   End If
End With
End Sub

You can adjust "dd/mm/yy hh:mm:ss" to whatever you need.
Wayne    09 May 2015, 22:15
Good formula which works as described; however, only the time is showing in the cell and what if I need a second column to use the same function?

i.e. input data into a cell in column a (date and time) displayed in column in adjacent cell in column b. on the same line I then need data entered into column c and the date time this data was entered in column d.

is it possible to do a macro for this or a formula to cover this?
Vail     29 Apr 2015, 02:00
Thanks for the help. couldn't figure this one out all day and you made it appear simple. thank you.
Barry    24 Apr 2015, 05:48

See comments below on setting the formatting of the cell to display date and/or time


When Sharing workbooks to allow multi-user editing, core parts of basic functionality in Excel is effectively restricted.

Any VBA code to access these components will consequently fail and result in errors, as you are experiencing.

The article on the Microsoft Office website gives a list of restricted functions which macros should not use: http://tinyurl.com/lkawaop
aaditi    24 Apr 2015, 02:11

I want to get of current time and date entry for any editing/addition in excel in a shared and protected workbook for multiple sheets. Above macro is working fine in a normal protected sheet. but when it becomes shared the macro gives error. Please help!


Pape    21 Apr 2015, 11:00
How can I have the time display only in cell A when I write in cell B.

Barry    18 Apr 2015, 05:18
You can create a Worksheet_Change event on the Worksheet where the input is made, and use that to invoke the record updating macro on the other Worksheet.

However, there is a major gotcha in that the formula may be such that the value doesn't actually change but the date will be updated (in a similar manner that editing but not changing a value would as described by Micky Avidan below).

I haven't tested it but the same may be true for dynamic functions like NOW() but I doubt if these would be used in the scenario described.

Another way is to use the ONTIMER function to check for a change, say every 1 second. In this case you'd definitely have to keep the old value for every cell being monitored and could be become very processor intensive if there are lots of cells being monitored.
Willy Vanhaelen    16 Apr 2015, 12:51
The Worksheet_Change event only fires when you enter something in a cell or delete one. It is NOT fired when the value of a formula changes as a result of entering something in another sheet for instance. So in such a case the macro will not work.
Mihkel    15 Apr 2015, 10:44
I have the same problem.. Frustrating!

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.
Barry    08 Mar 2015, 07:41

Put this code on to the codepage where you want to record the dates for data changes.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then Cells(Target.Row, Target.Column + 1).Value = Now
End Sub

As noted below if the user selects the cell and starts editing it but then abandons the edit so that the cell doesn't change the date will still be updated. If this is an issue then you need to modify the code as suggested by Micky Avidan below.

Barry    07 Mar 2015, 05:40

I do not understand your problem.

If you just want to import the data in the format you specify then I would suggest you use the Text-To-Columns feature using an underscore as the delimiter or if it is in a file use the Text Import feature again using the underscore as a delimiter
Barry    07 Mar 2015, 05:35

Put the following code on the code page for the worksheet holding your data:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F8:AB3000")) Is Nothing Then Cells(Target.Row, 5).Value = Now
End Sub
Dev    07 Mar 2015, 04:06
I have excel sheet, in which data arrieved from somewhere in format ie.DD/MM/YY_____DATA.
In data sheet date is change automatically on regular basis and data value also.
can i able to record data automatically.
its urgent plz help me
Adriane    06 Mar 2015, 15:43
Thank you for this article, and all of the insightful comments.This code is great. I'm no developer either, although I have played around in VBA a bit. I really like the edits made to Fraser's code, however, how would one set this to populate into a defined area? For example, if anything in f8:ab3000 is modified, populate column E in the corresponding row in which the change was made. I tried editing the code provided by Barry, and unfortunately cannot get it to work. Any help would be immensely appreciated.
obaydul    01 Mar 2015, 05:42
This macro is very much helpful but I've to add multiple column like B records the changes in A, E records the changes in D and goes on, it takes only two column. when I adds more than 2 column it shows

"Compile error:
Wrong Number of argument or invalid property assignment"

Any help will be much appreciated
John    28 Feb 2015, 18:06
I have a special case: on another program, a switch is toggled and the target cell changes from 0 to 1. Any idea why the code doesn't work?

I've tried changing the target cell from "general" to "number" but no luck. I'm thinking it's something involving the formula.

Also, is there a code to use one target cell and record changes down one single column instead of rewriting.
Fraser    27 Feb 2015, 05:20

Thanks ever so much, this has helped me enormously.

Barry    26 Feb 2015, 07:19

An even easier way is to use the function:

Barry    26 Feb 2015, 06:39

To get the Windows Username you have to use a Windows API call. The function below when called will return the Windows Username.

Declare Function Get_User_Name Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, _
nSize As Long) As Long

Function GetUserName() As String
     Dim lpBuff As String * 25

     Get_User_Name lpBuff, 25
     GetUserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
End Function

The "Declare Function" statement needs to be at the top of a code Module.
DianaH    25 Feb 2015, 21:48

Beautiful coding, works like a charm.

At the moment, your code adds a new timestamp to the right of the cell that was modified. Would you by any chance know how to adjust the coding so that any time a cell in column A is modified, then to only have 1 specific cell (for example, cell B46) to update the existing timestamp?

Thank you so much.
Fraser    20 Feb 2015, 00:44

Thanks your version was way better and worked straight away, just one quick question is how would I add the windows username to this as well, as I have multiple people editing the spreadsheet.


Fraser    19 Feb 2015, 05:05
Thank you Barry,

I will give it a go, the issue here is I have a spreadsheet that is actually quite long and shared, and I need to know when any rows have been updated, ideally I would also like t tag the user but am struggling as it is :-)

Barry    18 Feb 2015, 08:30

This macro must be resident on the codepage of the worksheet that it is applicable to i.e. Sheet1

You can delete the line Worksheets(Sheet1).activate the syntax is wrong, and it superfluous.
There is also an error on the line "Set myRngToInspect = Range("a4:44")" this should read "Set myRngToInspect = Range("a4:p4")"

The code at first sight seems to be overly repetitive, but it does handle the case where multiple cells are selected across two or more rows and a value is enter using Ctrl+Enter (which enters the value into all the selected cells). If this is not an issue then the code can be simplified to:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A3:P5")) Is Nothing Then Cells(Target.Row, 18).Value = Now
End Sub

Again this must be on the codepage of Sheet1.

This still suffers from the issue Micky Avidan mentions below. In that overwriting the cell value to the same value will update the date/time stamp.
Fraser Larcombe    18 Feb 2015, 02:19

I am trying to insert a date in a cell when anything in the row has been changed, I am not a programmer and have tried a few things on the web but nothing yet has worked.

This was the closest I have got, it did go through the debug ok, but it never seems to do anything and I cant run it from a macro as none show on the pull down list, I am sure its something simple but haven't been able to work out what it is, can you help?

Option Explicit
Public Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToInspect As Range


Set myRngToInspect = Range("a3:p3")

If Intersect(Target, myRngToInspect) Is Nothing Then
GoTo Line4
End If

Application.EnableEvents = False
Range("r3").Value = Now
Application.EnableEvents = True

Set myRngToInspect = Range("a4:44")

If Intersect(Target, myRngToInspect) Is Nothing Then
GoTo Line5
End If

Application.EnableEvents = False
Range("r4").Value = Now
Application.EnableEvents = True

Set myRngToInspect = Range("a5:p5")

If Intersect(Target, myRngToInspect) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
Range("r5").Value = Now
Application.EnableEvents = True

End Sub

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.


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–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2015 Sharon Parq Associates, Inc.