by Allen Wyatt
(last updated August 30, 2014)
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:
Figure 1. The Formulas area of the Excel Options dialog box.
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.
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!
When you enter a time value into Excel, the program tries its hardest to make the value into a valid time. This can lead ...Discover More
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
Dealing with times in Excel is fairly straightforward, except when it comes to midnight. Some people prefer that midnight ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.