Automatically Entering a Data Entry Time

by Allen Wyatt
(last updated March 28, 2020)

7

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 or later versions 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. (This places the macro in the ThisWorksheet module, which is required as it is an event handler that is executed whenever something in the worksheet changes.)

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"

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

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

Printer Won't Print Envelope Font

The default font used for envelopes is called Callibri. What do you do if you cannot print that font on your printer? ...

Discover More

Workbook Events

You can create macros that run whenever Excel detects a certain event happening within an entire workbook. This tip ...

Discover More

Creating Individual Workbooks

Workbooks can contain many worksheets. If you want to pull a workbook apart and create a whole series of workbooks based ...

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)

Dealing with Large Numbers of Seconds

When adding values to a time to calculate a new time, you may naturally choose to use the TIME function. This can cause ...

Discover More

Automatically Converting to GMT

You know what time it is, right? (Quick; look at your watch!) What if you want to know what time it is in Greenwich, ...

Discover More

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 ...

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 8 + 1?

2020-06-11 21:16:28

John

Hi,

Is there a way to use this Macro code but make the timestamp update when new information is entered into the B cell?
This is a shared workbook so people add text information without removing the existing text.

Thanks,
John


2020-04-29 07:31:31

Peter Atherton

James
Your format may be wrong. To display Date & Time use the custom formt dd/m/yyyy h:mm
If you only want the time change the format to [h]:mm:ss
However, the formula seems to display the value in B15 when it is not empty. You might find this better:
=IF(AND(A23<>"", B23=""),NOW()-INT(NOW()), "")
HTH


2020-04-28 05:12:08

james

When I enter the formula the date keeps coming back 0-Jan-1900, it won't display correctly. Now if the referenced cell is blank, it displays blank as it's supposed to. Here is my formula =IF(A15<>"", IF(B15="",NOW(),B15), "") I believe it's exactly as above. Do I need a space or remove a space somewhere? I'm not sure what I'm doing wrong. I'm using Excel 2016. Thanks for any advice why this is happening. James


2020-04-21 10:47:15

J. Woolley

@Varghese
Change the first line of the macro from
Private Sub Worksheet_Change(ByVal Target As Range)
to
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
and put the macro in the ThisWorkbook class. (In Visual Basic Editor, right-click ThisWorkbook and pick View Code.) The new macro will apply to every sheet in the workbook.


2020-04-21 02:02:00

Varghese

Hi,
How to make it (Automatically Entering a Data Entry Time) to use the entire workbook rather than a single sheet any method ?

Cheerfully

Varghese


2020-03-30 16:13:55

Allen

Peter,

Two things. First, about the focus of this site relative to Excel for the Mac. You will find, in this regard, the FAQ helpful, here:

https://excelribbon.tips.net/faq.html

You'll want to pay particular attention to the 3rd Q&A combo.

Now, as to how you get to these sort of settings on the Mac. You will need to click on the Excel menu, then choose Preferences. That displays the Excel Preferences dialog box from which you should then click Calculation.

Hope that helps, Peter.

-Allen


2020-03-30 12:53:03

Peter Clarke

In your step-by-step method of displaying a sustaining date and time in a cell you say, "Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 or later versions display the File tab of the ribbon and then click Options.)". I am running Excel for Mac 2019 - version 16.35, Product ID: 04242-000-000004, and I cannot see where the 'File tab of the ribbon' is. Nor can I find an Options anywhere but AutoCorrect Options. Are your tips and assistance only relevant to Windows OS products? I was under the impression when I signed up for your tips that they were Mac OS relevant also. Please assist.

Kind regards,
Peter Clarke - plclarke@xplornet.com


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.