Written by Allen Wyatt (last updated March 28, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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. (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:
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 Microsoft 365.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
When using Excel to calculate elapsed time, there can be all sorts of criteria that affect the formulas you would ...
Discover MoreWhen adding values to a time to calculate a new time, you may naturally choose to use the TIME function. This can cause ...
Discover MoreIt is no secret that you can store time values in an Excel worksheet. But do you really know how small of a time value ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-05-11 04:20:51
Jess
I was looking to repurpose this code to create a time tracking system for when I clock in, clock out for lunch, clock back in from lunch, and then finally clock out. Ideally, I would just place a value (ex: "x") into the cell this formula is looking for so a timestamp can be created in the adjacent cell. It would be really nice to have this on a mobile device given I don't sit at a desk often and the tasks change daily.
I've tried both methods.
It seems that I get, "this version of Microsoft Excel can't run VBA macros" when using the Excel app for Android (when using the noniterative calculations method.
When using the iterative calculations method, and running the file from the Excel app for Android, it's clear that the settings to allow iterative calculations were not transferred and I'm not seeing a setting to enable them.
I've yet to try google sheets, but sometimes my reception at work is poor is none at all. Additionally, google sheets seem like it doesn't always have all features found in Microsoft 365 Excel.
I would be grateful for any additional avenues to look into to integrate excel formulas and macros into a mobile data entry format.
2021-02-14 13:02:23
Willy Vanhaelen
Obviously, this can only be solved with a macro. The one presented here does a fine job although is has a small bug. When you insert or delete an entire row, the cell in column A always gets the current date.
It also uses a For Each loop which isn't necessary. Donna specifies that she has to document calls that come in over the radio. So she has to enter each call in a row call by call. It is not possible to enter more than one call at the time because the time will never be the same. So I rewrote the macro without the loop and the bug fixed:
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
Target.Offset(0, -1).NumberFormat = "hh:mm"
End If
End Sub
2020-08-03 13:12:52
J. Woolley
You might be interested in my TimeStamp(Target) function that uses an unthreaded Comment (a.k.a. Note) to time stamp any cell whenever that cell's value changes. For example, to time stamp cell B2 whenever its value changes, put this formula in any cell C on the same worksheet:
=TimeStamp(B2)
That formula returns Null if C is B2; otherwise, it returns this text:
TimeStamp($B$2)
When the value of cell B2 changes, a new time stamp Comment is added to cell B2 replacing any previous Comment. If cell B2 is on Sheet1, then this formula can be in any cell of any other worksheet in the same workbook:
=TimeStamp('Sheet1'!B2)
That formula returns this text:
TimeStamp('Sheet1'!$B$2)
The function does not need to be alone in a formula; it can be part of a more complex formula such as:
=HYPERLINK("#B2",TimeStamp(B2))
That formula in cell C (where C is not B2) creates a link to cell B2 and returns this text:
TimeStamp($B$2)
The TimeStamp function should be located in a Code Module of the workbook where it is used. If the function is located in Excel's PERSONAL.XLSB or an Add-In but used in another workbook, then all of that workbook's time stamps will update each time it is opened.
The TimeStamp function can be downloaded from My Excel Toolbox at https://sites.google.com/view/MyExcelToolbox
2020-08-01 04:56:26
Peter Atherton
Jarred, Look up Date Serial for detailed description. Dateserial start from 1 and are added to for each day that passes. Enter 1 and format as a date and this will tell you when your dates start. Time is a fraction of a day the formula =TIMEVALUE("6:00") returns 0.25
2020-08-01 04:34:57
Peter Atherton
If you want to enter the current date or time use these shortcuts.
Date: Ctrl +;
Time:= Ctrl + Shift + :
(see Figure 1 below)
Figure 1. Date and Time with functions
2020-07-31 14:49:58
Jared
When I use this formula (and the "correction attempt" below) I get 44043. Or, more recently, 44043.5707. The date is 7-31-20 and the time is 1:48pm. I cannot figure out how 44043.5707 translates to that date, I feel like it doesnt.
I have Donna's issue exept I don't really need the time. I just want to know the date an "idea" was entered into the spreadsheet so we can keep track of how long an item has been there. Short of entering the date manually. I don't want it to change if I update the or add to the ideas.
2020-07-22 05:25:07
ahmad elsarji
hello
im making this system so i want a bit of help concerning the time.
this is the closest thing to what i need, but im guessing its something other than the now() function
* =IF(B2<>"", NOW(), "") *
i have tried using now() and time and today formulas but the thing is its updating for all the cells below to time now if you know what i mean
i want the time when i entered information in B2 i dont want it to keep on updating every time i enter information in column B
thanks in advance
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
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments