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

Recording a Data Entry Time

by Allen Wyatt
(last updated November 30, 2013)

38

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:

=NOW()

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:

=IF(A3="","",IF(B3="",NOW(),B3))

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
            Else
                rCell.Offset(0, 1).Clear
            End If
        Next
    End If

ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12811) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Recording a Data Entry Time.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Repeating Your Typing

Want a quick way to repeat a word or phrase you just typed? Here's the shortcut you need.

Discover More

Hiding Rows Based on Two Values

It's easy to use filtering to hide rows based on the value in a cell, but how do you hide rows based on the values in two ...

Discover More

Macro Runs Slowly, but Steps Quickly

When you have a macro that processes a huge amount of data, it can seem like it takes forever to finish up. These slowdowns ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Limiting Number of Characters in a Cell

Need to limit the number of characters that can be entered into a cell? One easy way to do it is through the use of Data ...

Discover More

Highlighting the Rows of Selected Cells

If you lose your place on the screen quite often, you might find it helpful to have not just a single cell highlighted, but ...

Discover More

Showing Text when a Cell is Empty

Wouldn't it be great if you could have Excel display some text in a cell only when that cell is empty? Unfortuantely, Excel ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 9?

2016-09-28 06:39:32

Willy Vanhaelen

@Peter Machado

You can use my version of the macro I posted on 30 Nov 2013. It doesn't have this bug.


2016-09-28 00:44:37

Peter Machado

Hi

I noticed if I delete the record, then the subsequent record now becomes the new current record, and it's time stamp gets updated. Can you put in a check for delete so if the record already existed it doesn't get updated?


2016-09-11 08:28:24

Chuck

Thank you.
VERY Clear and Concise.


2016-04-22 04:38:31

Nikhil

I want to make time sheet for full day meeting. There are several attendee who come and go frequently.
I want two button on top of two column for IN and OUT button named for each attendee and when pressed, it enters the current time. for next event when button is pressed, the data should be written to the next row for each attendee. this goes on for our weekly meeting for years to come.


2016-02-09 06:26:31

Willy Vanhaelen

@Peter Wessel

In which column do you want the date to appear?


2016-02-08 09:34:07

Peter Wessel

My amateur radio log I set up in Excel, and it works fine. But as I am going to take part in a radio contest, I need to submit a log in another format - also quite simple to set up. A contest is fast, and seconds count, so I found your macro suggestion for the automatic time input - and it works beautifully - Thank you! However, due to the format of the log I need to auto-enter the time in a column BEFORE the one I enter data into. Would be super if I knew how to do that.....


2015-09-01 10:55:06

awyatt

Marissa: It happens because running a macro always clears the "undo stack." In other words, after running a macro you can't undo anything that occurred before the macro.

There is no way around this behavior that I've been able to find.

-Allen


2015-09-01 10:19:16

Marissa

Hi, I found this script and love it - except it has apparently removed the ability to Undo an action in any column prior to the one I set the macro to run. Instead of Column A:A, I used E:E, because that's where I want the timestamp to be based on. However, not being able to Undo actions in columns A-E is a dealbreaker. Any tips on why this is happening and how to fix it?


2015-07-27 05:56:08

Barry

@Peter,

The macro to do what you want is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B13")) Is Nothing Then Range("J10").Value = Now
End Sub

Please note that this needs to be entered on the codepage for the worksheet that the two cells reside upon.

Note: also the B13 is updated if J10 is edited and the value remains unchanged after the edit, or if the value is change to the same value. If this is a problem then then following slightly more complicated pair of macros will work:

Public TempVal as Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target,Range("J10") is Nothing Then TempVal = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target,Range("J10") is Nothing Then Exit Sub
If Target.Value <> TempVal Then Range("B13") = Now
End Sub

Again these macros need to reside on the codepage for the worksheet concerned. NB TempVal is declared outside of any macro.


2015-07-27 05:36:54

Willy Vanhaelen

@Peter
Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$13" Then Exit Sub
If Target <> "" Then
Range("J10") = Now
Range("J10").NumberFormat = "hh:mm:ss"
Else
Range("J10").Clear
End If
End Sub


2015-07-26 21:00:23

Peter

Hi I would like to use something similar. But i want for example Cell J10 to contain the date that will auto update and change when data in B13 is changed. Is it possible to do it for cell specific instead of whole row or column


2015-06-09 05:34:47

Barry

@Dipak,

Just format the whole column using the time format that you want.

The value returned by the "Now" function includes the time as well as date.


2015-06-08 23:36:51

Dipak

hi,
i am in process engineering
i want to add my data in time formate (hh:mm) in developer code as a range


2015-06-08 06:40:51

Barry

@Alex,

Sorry for the typo, the code should be:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F4:O91")) Is Nothing Then Range("P" & Target.Range("A1").Row).Value = Now

End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F4").CurrentRegion) Is Nothing Then Range("P" & Target.Range("A1").Row).Value = Now

End Sub

Note also if the change is made across multiple rows only the first row will have its timestamp updated.


2015-06-08 06:32:43

Barry

@Alex

The following code will do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F4:O91")) Is Nothing Then Range("P" & Targetrange("A1").Row).Value = Now

End Sub

If your range is changing as new records are added/deleted instead of using the fixed range "F4:O91" use "Range("F4").CurrentRegion"

This must be on the codepage of worksheet containing the data.


2015-05-07 19:34:56

Alex Guerra

Hello,
I am wanting to have a date stamp anytime there is a cell changed/updated within each row. For example row 4 through 91 and columns f through o have data. I want the date at then end of each row, in column p. thanks


2015-04-15 05:15:17

Florence

Hello again,

In the meantime I've found the following code that is perfect for me (I need the time of the scanning of some barcodes) as it does a "copy/paste value" of the Time so that my records are fixed once for all.
I've even added a second With in which I add the date on a third column.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
With Target(1, 2)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub

Thanks for all the tips on this website!!!


2015-04-15 04:49:03

Florence

Hello,

I'm trying to use the code given by Willy on 30 nov 2013 (see here below) but I have an issue.

Starting from an empty cell, when I entered some data, nothing happens but if I go back in the cell where I've entered the data, then the time will appear - why is that? Could anyone help me?

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


2015-03-23 10:58:34

Glenn Case

Angela:

To do what you want, replace the For...Next loop with the following:

For Each rCell In rChange
If rCell > "" Then
If rCell.Offset(0, 1) <> "" Then GoTo ExitHandler
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With

Else
rCell.Offset(0, 1).Clear
End If
Next


2015-03-15 23:49:35

Craig

I am wanting to write customer details in column A, and last name in column B

When I do this I want an automated date in C, and time in D.

When I click in E I need an automated time (this will be the end of the job).

And then have F calculate the time taken between D and E

Could some please assist with this.

Thanks :)


2015-03-15 18:11:13

Angela

I apologize in advance for commenting on an old tip.

I'm using something very similar to the above code, but I have a small problem.

I want the time entered into column A when text is entered into column B, but I don't want the time changed if you go back to a previous line to edit column A. Is this possible with Excel, and if so can you assist me with the proper code?

I've been searching for a week, and I'm at wit's end!


2014-08-06 05:19:27

Jenny

Hi,

I would like to record changes made to the data in columns 1,6,7,8,9 solely in column 10, could anyone help with this please?


2014-02-19 11:12:47

Bryan

@Willy: tbh I didn't think about what the first line meant, so I didn't bother with it. I didn't want to do ALL the work for Sky ;-)

@Sky: without any details there's not really any way to help.


2014-02-19 07:20:02

Willy Vanhaelen

@Bryan: your solution is the classic one and will of course work fine except when you delete an entire row. Then you will get an error: .Offset(0,1).Clear doesn't work with an entire row. Therefore you must disallow the macro to be executed when more than one column is selected. Hence "... Or .Columns.Count > 1 Then Exit Sub" in my macro.


2014-02-19 02:01:19

Sky

Thanks both,exactly what I needed, I tried to protect the sheet except the columns in which the data should be entered manually and the date column but then a debug message appears and it doesn't work anymore.


2014-02-18 11:10:10

Willy Vanhaelen

@Sky: If you use my macro (see comment 30 Nov 2013) then replace

If .Column <> 1 Or .Columns.Count > 1 Then Exit Sub

With

If InStr("1,3,5", .Column) = 0 Or .Columns.Count > 1 Then Exit Sub

If you now enter data in column A, C or E, the date will appear in column B, D or F respectively.


2014-02-18 10:56:38

Bryan

Sky, I'm not really sure what you are asking. If you want the rule to apply to another column then use another column reference.

Or do you want to put the time in B when A is changed, D when C is changed, and F when E is changed? Use Willy's code with a slight modification:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
Select Case .Column
Case 1, 3, 5 ' Column A, C, or E
If .Value > "" Then
.Offset(0, 1) = Now
Else
.Offset(0, 1).Clear
End If
End Select
End With
End Sub


2014-02-18 05:50:28

Sky

I mean how the formula changes if I want that rule applies to columns a,c,e at same time.


2014-02-18 01:22:07

sky

anyone?


2014-02-16 09:39:09

Sky

what about if I want to do the same thing for columns C,E & F? I mean as soon as I put data in C then the time of data entry appears in D cell next to it and so on. But altogether at same time not just one column.


2013-12-03 13:37:03

Bryan

Nitin: Instead of going into the sheet module, you would instead go into the ThisWorkbook module and add the following code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Sh.Range("J1")) Is Nothing Then Exit Sub
Sh.Range("J1").Value2 = Now
End Sub

If you only wanted a few sheets to update (say, Sheet1 and Sheet2, but not Sheet3), you could do something like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Sh.Range("J1")) Is Nothing Then Exit Sub
If Not InStr(1, "Sheet1;Sheet2;", Sh.Name & ";") > 0 Then Exit Sub
Sh.Range("J1").Value2 = Now
End Sub

Note that the more hard-coding of data you do, the more likely you (or your user!) are are to break it without realizing it. For instance, in the second macro, if a user changes Sheet1 to IncomeSheet, then the last edit date won't update anymore.

There are certainly ways to get around this issue, but that's a separate problem. The examples I've posted should be good enough to get you started.


2013-12-03 03:11:58

Nitin Sheth

Hi Bryan,
Tried - It worked... But One question - If I have many sheets in the work book, & I want the same code to run for each sheet & the cell "J1" in each sheet to be updated with the 'now' - Do i need to copy the code on each sheet ? or is there a universal way to apply it to more than one or all worksheets at a time??


2013-12-03 03:03:38

Nitin Sheth

Thanks Bryan....
Shall try it right away!


2013-12-02 10:49:18

Bryan

@Willy: good job! Your solution is soooo much more elegant.

@Nitin: you would want something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J1")) Is Nothing Then Exit Sub
Range("J1").Value2 = Now
End Sub

This macro will update cell J1 any time any cell on the worksheet is changed. If you only want to update J1 when a certain range is updated (e.g. C9:C22), then try this instead:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C9:C22")) Is Nothing Then Exit Sub
Range("J1").Value2 = Now
End Sub


2013-12-02 08:20:39

nitin sheth

Hi,
I tried this & it does work, But i would like only a particular cell in that sheet to display the last edited record time.
which means that if any cell in the sheet is entered / modified, say cell J1 will display the last edit / entry time in the relevant sheet.
Please advise.
Regards,
Nitin Sheth


2013-12-01 11:21:03

Willy Vanhaelen

For column A:
If .Column <> 1 Or .Columns.Count > 1 Then Exit Sub

For column B:
If .Column <> 2 Or .Columns.Count > 1 Then Exit Sub

etc...


2013-12-01 10:51:52

Marcelo

Hi Willy,

Where do I put column name ? (A, B...)


2013-11-30 09:07:14

Willy Vanhaelen

Obviously this can't be solved by a formula. But the macro presented here… that's quite a macro :-)
There is no need at all for a For Each loop here. Target represents the selected range and you can use it accordingly. Neither must Application.EnableEvents be used here because the macro doesn't make any changes to the monitored column so that an endless loop can't occur.

The 27 lines macro above can be replaced by my rather simple 11 lines one that does its job more efficiently of course:

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


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.