Automatically Entering a Data Entry Time

by Allen Wyatt
(last updated August 30, 2014)

38

Donna needs to keep a log of activity that occurred throughout the day at her dispatch center. When a call comes in over the radio she needs to document who, what, why, etc. She also needs to document the time (in 24-hour format) that the call came in. Donna currently does all this on paper. She would like to create a worksheet that she can type into and have the time stamp automatically populate as soon as she enters text into the cell next to it in that row. When the cell next to it is empty she wants her time stamp cell to be blank. This way she can save time from checking the clock to write down the time.

In approaching this problem, let's assume that you want the time stamp to go into column A and you'll be entering your "who, what, why," and other info starting in column B. The simplest way to go about this is to use Excel's built-in shortcut for entering the current time: press Shift+Ctrl+; (that's a semicolon). Provided the cell is formatted to show times in 24-hour format, you'll see the current time as you want it.

If you want a more automatic approach, you might think that you could use a simple formula in column A, such as this:

=IF(B2<>"", NOW(), "")

While this will enter a time into column A if the corresponding cell in column B contains something, it won't give satisfactory results because of how the NOW function works. Remember that the function always returns the current time. Thus, anytime the worksheet is recalculated, the contents of cell B2 are checked. If there is something there, then the NOW function is called, returning the current time of the recalculation. This means that the time shown in the column A cell containing this formula will always be changing; it won't be a true time stamp.

You could, however, make the formula in column A a bit more complex, in this manner:

=IF(B2<>"", IF(A2="",NOW(),A2), "")

The very first time you enter this, it is likely you'll get an error because the formula is circular. In other words, the formula references the cell in which the formula is placed. (In this case, the formula goes into cell A2 and also references cell A2.) To make this formula work properly, you need to follow these steps:

  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 and Excel 2013 display the File tab of the ribbon and then click Options.)
  2. Click Formulas at the left side of the screen. (See Figure 1.)
  3. Figure 1. The Formulas area of the Excel Options dialog box.

  1. Make sure the Enable Iterative Calculation check box is selected.
  2. Click OK.

Now, whenever you put something into cell B2, the time is entered automatically into cell A2. However, the time is only put there if cell A2 was previously blank. If it wasn't (meaning it already contained a time), then the current contents of cell A2 remain in place.

If you don't want to allow circular references (by turning on iterative calculations), then the best approach is going to be through a macro. You can use the Worksheet_Change event to automatically enter a time into column A whenever something is entered into column B.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range
    Dim tCell As Range

    Set rInt = Intersect(Target, Range("B:B"))
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            Set tCell = rCell.Offset(0, -1)
            If IsEmpty(tCell) Then
                tCell = Now
                tCell.NumberFormat = "mmm d, yyyy hh:mm"
            End If
        Next
    End If
End Sub

To set up the macro, right-click on the worksheet tab and choose View Code. You can then place the macro into the code window that is displayed.

The macro checks to make sure that something is actually being entered into column B. If so, then the tCell variable is set to the corresponding cell in column A. If nothing is already in that cell, then the current date and time is placed there. If you just want the time, then change the innermost part of the macro to this:

                tCell = Time
                tCell.NumberFormat = "hh:mm"

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13233) applies to Microsoft Excel 2007, 2010, and 2013.

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

Storing AutoText Entries with a Document

AutoText entries can provide quite a bit of flexibility and power in a document. If you want to share those entries with ...

Discover More

Printing Color Separations with VBA

When printing in color (at a commercial printer) it is necessary to print different colors of your document in different ...

Discover More

Two Types of Page Numbers in a TOC

Word, when creating a table of contents, should automatically make sure that the page numbers it shows correspond to the way ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Displaying a Result as Minutes and Seconds

When you use a formula to come up with a result that you want displayed as a time, it can be tricky figuring out how to get ...

Discover More

Checking for Data Entry Errors for Times

When you enter a time value into Excel, the program tries its hardest to make the value into a valid time. This can lead to ...

Discover More

Checking for Time Input

Need to know if a cell contains a time value? Excel doesn't contain an intrinsic worksheet function to answer the question, ...

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 7 - 0?

2017-09-17 15:20:40

Mary

This is just wonderful. Thank you very much.


2017-05-01 12:12:03

DILSHAD AHMAD

Hi,
I want to now with logic of formula

Colom. A Name is Modi
Colom. B Name is Manoj
Colom C Name is Time

Colom C When I Put Only T Time and date automatic came how.

Please give feedback I awaiting your reply


2017-03-16 23:31:45

jai

also i need to protect the sheet with allow ranges


2017-03-14 12:31:27

Willy Vanhaelen

@Trina
This macro should do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Or Target.Cells.Count > 1 Then Exit Sub
Target.Offset(0, -2) = Date
Target.Offset(0, -1) = Time
End Sub

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


2017-03-13 19:45:46

Trina

REVISED

Thank you very much Micky and Barry :)

Barry, I need 2 columns, one for the date, and another for the time. I could format the cells to display the time only, but it leaves me one column short.
Micky, I tried your code but it didn't work for me - perhaps I was supposed to add it to the existing code?

To clarify, I'd like cell A1 to be the date and cell B1 to display the time when data is entered in cell C1. This format will give me the ability to produce reports for my needs, and my staff to clearly see what I'm trying to achieve: exactly what this article is instructing, except with one additional column.

Thank you again for your help!
Trina


2017-03-13 19:43:59

Trina

Thank you very much Micky and Barry :)

Barry, I need 2 columns, one for the date, and another for the time. I could format the cells to display the time only, but it leaves me one column short.
Micky, I tried your code but it didn't work for me - perhaps I was supposed to add it to the existing code?

To clarify, I'd like cell A1 to be the date and cell B1 to display the time when data is entered in cell C1. This format will give me the ability to produce reports for my needs, and my staff to clearly see what I'm trying to achieve.

Thank you again for your help!
Trina


2017-03-10 04:01:35

Michael (Micky) Avidan

@Trina,
If formatting the result cell(s) doesn't meet your needs - try the following code:

Sub Micky()
[A1] = Now ' Date_Time
[A2] = Date ' Date_Only
[A3] = Time ' Time_Only
End Sub

--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL


2017-03-10 03:08:59

Barry

@Trina,

I would just format the cells to show the time. Even though the cell itself will have the full date serial number. This will cope with finishing times that are on the next day (or a few days later) which just storing the time of day will not do.


2017-03-09 17:21:14

Trina

What helpful comments - thank you everyone for sharing your knowledge!!

I've been reading through the articles and cannot locate any tips on vba code to split the date from the time. For example, I'd like cell A1 to state the date and cell B1 to state the time when I enter data into column c1. This way I can use pivot tables to analyze how long it took an employee to complete a task.

Does anyone happen to have vba code for this scenario or advise how it can be split?


2017-02-01 12:25:40

jonas

the time comes in decimal points... i did exactly as shown above


2016-11-07 02:17:24

gerad

Didn't work for me kept insert 12:00 regardless of the pc time


2016-10-24 05:03:47

Joms

Whenever i accidentally put some text or delete it from the target cell the time keep on popping even if i deleted the character inside the Cell?


2016-10-22 05:30:18

Willy Vanhaelen

@baba

See my comment of 24 Feb 2016.


2016-10-21 10:47:10

bala

above code ok, but i want "if i entry particular text then only denote date not all entry" how?


2016-10-10 19:15:14

Paul Kelly

I've noticed in using this macro that an error pops when the cells around the tCell destination area are protected.

However, when the cells in general are not protected, it works fine.

Any suggestions on how this Macro can be re-scripted to compensate for a protected sheet??

Thanks


2016-08-05 16:41:34

cal

excellent! thank you so much!


2016-04-08 17:10:36

Doug

I'll do my best to keep this as concise as possible...

I am creating a jobsite accounting spreadsheet for the construction company I work for. On one tab, I have a column showing a set of dates that we are scheduled to work on the project, and in the column next to it, I have the number of man hours that were worked on that date. This is repeated on several tabs for specific areas people are working in.

I have another individual tab dedicated to showing the number of man hours worked in each area, all in one column (in summation). Is there any way that I can have a column next to it that updates automatically every time I enter the man hours in the other tabs?


2016-03-23 08:48:18

Willy Vanhaelen

@Falak

Read these comments and you will find your solution in my post of 21 Feb 2016 (@Lee).


2016-03-22 06:18:29

FALAK

i need VBA code to ENTER DATA IN CELL A1 AND GET CURRENT DATE IN CELL A2


2016-02-24 11:35:28

Willy Vanhaelen

@Lee

I have no experience at all with shared workbooks, so I can't help you on that.

When you enter "Quality Control" in one or more cells in column B, the following macro will automatically insert the date/time in the corresponding cell(s) in column A.

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

Hope this helps.


2016-02-23 10:46:27

Lee

Hi Willie.

How do macros usually fare on shared sheets? I need a trouble free approach that is reliable.

Also, if I wanted to use a target word rather than just any text to trigger the event, what would I alter below? "Quality control" are my trigger words on my actual sheet.

Many thanks,
Lee.


2016-02-21 09:49:36

Willy Vanhaelen

Obviously this can better be solved by an event macro. But the one presented in this tip is much to complicated. There is no need at all for a For Each loop since it is assumed that the activity's are loged one by one.

This simple two lines macro does the job equaly well:

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

Excel automaticaly formats the cell in column A according to the computer's default date/time format. If you prefer another format you can pre-format column A accordingly.

@Lee

In my opinion, there is no good formula solution. If you use the macro approach and you prefer to use column A for the "activity" and column B to contain the time, use this macro:

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


2016-02-20 21:07:13

Lee

Hi.

Is there a way, using formula only, that I can record the time when a certain entry is made in a cell and also that it would remain in the box even after the reference cell value is changed after?

For example, when A1 has Apple entered in to it the time is recorded. When A1 value is changed from Apple to Orange the time stamp still shows the time Apple was entered and doesn't get updated to the time Orange was entered or that the stamp gets removed completely?

This is for use on a shared sheet, hence why I would prefer formula over Macro.

Hope that's clear?! :o)

Thanks in advance!


2016-01-27 10:15:14

Barry

@vijay,

The following code should do what you want, it assumes you have a"submit" button called btnSubmit

Private Sub btnSubmit_Click()
Dim sht2 As Worksheet
Dim EntryRow As Long

'Define the columns for each textbox or other control
Const colTextBox1 As Integer = 1
Const colTextBox2 As Integer = 2
Const colTextBox3 As Integer = 3
Const colTextBox4 As Integer = 4
Const colTextBox5 As Integer = 5
'..add as many of these entries as you have text/combo/list boxes etc


'validate all required data has been entered and all values are acceptable

'...enter input validation code here


'Transfer validated entries to Sheet 2
Set sht2 = Worksheets("Sheet2")
With sht2
EntryRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(EntryRow, colTextBox1) = TextBox1.Value
.Cells(EntryRow, colTextBox2) = TextBox2.Value
.Cells(EntryRow, colTextBox3) = textbox3.Value
.Cells(EntryRow, colTextBox4) = textbox4.Value
.Cells(EntryRow, colTextBox5) = textbox5.Value
'...add extra entries for each text/combo/list box etc.
End With

Unload.me 'close the Userform
Set sht2 = Nothing

End Sub

If you have lots of fields to transfer between the Userform and the worksheet then the following code will loop through the controls. There is a very specific naming convention used to make this work, and that is the controls (text/combo/list boxes etc) are named as "DataNNN" where NNN is the column number where the data value in the control is to be transferred to. E.g. for the data in a control to be transferred to column "A" call the control "Data1". Here;s the code snippet:

Dim myControl As Control
Dim x as Long
...
EntryRow = sht2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For Each myControl In UserForm1.Controls
If Left(myControl.Name,4) = "Data" Then
x = Mid(myControl.Name, 4, 20)
sht2.Cells(EntryRow, x) = myControl.Value
End If
Next
In this case you will not need the list of DIM statements at the beginning of the routine either. The various control do lose the ability to a a more meaningful name however.


2016-01-26 12:21:54

vijay

Hi
I want to use automatic data entry from userform to sheet2 row with automatic add row option option, because of i need all old and new entry one by one


2015-11-06 11:07:45

Paul A Garraway

Great Help
Thank You


2015-09-07 11:18:36

Harishankar

Hi ,
Its very nice ..
but I want some change in it .

I want to auto update time

if some pest in cell A1 current time will auto update in B1 cell..
and if again we are pest some in a2 cell then current time will update in B2 cell.
for example ;
first i pest 781 in A1 time will update 8:41 ..then i pest 782 in A2 current time update B2 cell.
781 8:41 PM
782 8:46 PM

Please share formula for this ...



2014-09-05 17:18:35

Paul Malycha

Don,
I think you missed the point of the "Speak" method being used in the macro.
The date is still recorded in the column A cell to the left of the data entered into the column B cell, as soon as Donna enters the data in the column B cell.
How this would have, "a negative effect on company morale which then impacts efficiency and effectiveness", I am not quite sure?


2014-09-04 13:39:12

Dinesh

=IF(B2<>"", IF(A2="",NOW(),A2), "")

above will keep changing "Date & time" every time you open the worksheet


2014-09-03 10:02:15

Don

Jeffrey: It's ctl-;, just leave out the shift

Interestingly, the difference isn't just a matter of formatting. The Time is on day 0 of the calendar in use. The Date is set at 00:00:00 for the time of day.

As to using the Speak method, considering what I see coming in as voicemail voice-to-text I would not trust it.

Besides that, the need on a log such as this is not so much a transcript but a summary: who reported what, how that affects something, decisions made, follow up planned. Often there is additional conversation that goes on that may or may not be permanent to what is needed. <Soapbox ON> This is not just "data entry", it's a professional doing a business task.

Additionally, people are becoming wary of having too much of their life recorded. The off-topic discussions between a driver and a dispatcher are part of the culture of a company. Squelching that, because people are concerned their words are being captured at every turn, will have a negative effect on company morale which then impacts efficiency and effectiveness. <Soapbox OFF>




2014-09-03 06:23:56

Jeffrey Pfouts

Thank you Dena!


2014-09-02 10:38:06

Dena Norton

Jeffrey, the keystroke for the current date is CTRL+; (Control+semicolon).


2014-09-02 07:06:57

Jeffrey Pfouts

What is the keystroke for the date?


2014-08-31 03:09:35

Paul Malycha

If Donna is so keen to speedup her work I have included one extra line of code that will enable her to know exactly what time the entry was made without even looking at her work, just as most data entry operators work.
In the middle of the sub on a new line following the date entry, include the following line of code.

...
tCell.Speak
...


2014-08-30 19:03:04

Rowena Taylor

I didn't get to check this edition of Excel tips until the answers were shown, but why doesn't she just hold down Ctrl + Shift and the ; and it enters the current time. Seems easier to me.


2014-08-30 09:10:28

awyatt

Ralph: As Brian already mentioned, the macro won't work if it isn't in the ThisWorksheet class module. The easiest way to get to that is to follow the instructions right after the macro in the tip: "right-click on the worksheet tab and choose View Code."

Hope that helps.


2014-08-30 08:07:20

Brian Hershman

(1) The macro must be saved in the(class) module belonging to the worksheet that will contains the data. It will not work anywhere else.

(2) In VBA blank rows are just ignored. They are used simply to make the code more readable.


2014-08-30 07:09:41

Ralph Shumaker

I copied the above macro directly into a VBA Project and then tried to debug by stepping into the macro. Nothing happens.
What is the purpose of the Private in Private Sub Worksheet? In your earlier macros, the routine would not run until I removed Private from the command. What is the purpose of the blank row? Should there be a blank row without an "'" to make it a comment?

I also tried entering the macro from scratch. Nothing happened. No errors. No messages.


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.