Show
A timestamp is something you use when you want to track activities. For example, you may want to track activities such as when was a particular expense incurred, what time did the sale invoice was created, when was the data entry done in a cell, when was the report last updated, etc.
Let’s get started. Keyboard Shortcut to Insert Date and Timestamp in ExcelIf you have to insert the date and timestamp in a few cells in Excel, doing it manually could be faster and more efficient. Here is the keyboard shortcut to quickly enter the current Date in Excel: Control + : (hold the control key and press the colon key). Here is how to use it:
A couple of important things to know:
Note that this is not dynamic, which means that it will not refresh and change the next time you open the workbook. Once inserted, it remains as a static value in the cell. While this shortcut does not insert the timestamp, you can use the following shortcut to do this: Control + Shift + : This would instantly insert the current time in the cell. So if you want to have both date and timestamp, you can use two different cells, one for date and one for the timestamp. Using TODAY and NOW Functions to Insert Date and Timestamps in ExcelIn the above method using shortcuts, the date and timestamp inserted are static values and don’t update with the change in date and time. If you want to update the current date and time every time a change is done in the workbook, you need to use Excel functions. This could be the case when you have a report and you want the printed copy to reflect the last update time. Insert Current Date Using TODAY FunctionTo insert the current date, simply enter =TODAY() in the cell where you want it. Since all the dates and times are stored as numbers in Excel, make sure that the cell is formatted to display the result of the TODAY function in the date format. To do this: Note that this formula is volatile and would recalculate every time there is a change in the workbook. Insert Date and Timestamp Using NOW FunctionIf you want the date and timestamp together in a cell, you can use the NOW function. Again, since all the dates and times are stored as numbers in Excel, it is important to make sure that the cell is formatted to have the result of the NOW function displayed in the format that shows the date as well as time. To do this:
This would ensure that the result shows the date as well as the time. Note that this formula is volatile and would recalculate every time there is a change in the workbook. Circular References Trick to Automatically Insert Date and Timestamp in ExcelOne of my readers Jim Meyer reached out to me with the below query. “Is there a way we can automatically Insert Date and Time Stamp in Excel when a data entry is made, such that it does not change every time there is a change or the workbook is saved and opened?”This can be done using the keyboard shortcuts (as shown above in the tutorial). However, it is not automatic. With shortcuts, you’ll have to manually insert the date and timestamp in Excel. To automatically insert the timestamp, there is a smart technique using circular references (thanks to Chandoo for this wonderful technique). Let’s first understand what a circular reference means in Excel. Suppose you have a value 1 in cell A1 and 2 in cell A2. Now if you use the formula =A1+A2+A3 in cell A3, it will lead to a circular reference error. You may also see a prompt as shown below: This happens as you are using the cell reference A3 in the calculation that is happening in A3. Now, when a circular reference error happens, there is a non-ending loop that starts and would have led to a stalled Excel program. But the smart folks in the Excel development team made sure that when a circular reference is found, it is not calculated and the non-ending loop disaster is averted. However, there is a mechanism where we can force Excel to at least try for a given number of times before giving up. Now let’s see how we can use this to automatically get a date and timestamp in Excel (as shown below). Note that as soon as I enter something in cells in column A, a timestamp appears in the adjacent cell in column B. However, if I change a value anywhere else, nothing happens. Here are the steps to get this done: That’s it! Now when you enter anything in column A, a timestamp would automatically appear in column B in the cell adjacent to it. With the above formula, once the timestamp is inserted, it doesn’t update when you change the contents of the adjacent cell. If you want the timestamp to update every time the adjacent cell in Column A is updated, use the below formula (use Control + Shift + Enter instead of the Enter key): =IF(A2<>"",IF(AND(B2<>"",CELL("address")=ADDRESS(ROW(A2),COLUMN(A2))),NOW(),IF(CELL("address")<>ADDRESS(ROW(A2),COLUMN(A2)),B2,NOW())),"") This formula uses the CELL function to get the reference of the last edited cell, and if it’s the same as the one to the left of it, it updates the timestamp. Note: When you enable iterative calculations in the workbook once, it will be active until you turn it off. To turn it off, you need to go to Excel Options and uncheck the ‘Enable iterative calculation’ option. Using VBA to Automatically Insert Timestamp in ExcelIf VBA is your weapon of choice, you’ll find it to be a handy way to insert a timestamp in Excel. VBA gives you a lot of flexibility in assigning conditions in which you want the timestamp to appear. Below is a code that will insert a timestamp in column B whenever there is any entry/change in the cells in Column A. 'Code by Sumit Bansal from https://trumpexcel.com Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True End If Handler: End Sub This code uses the IF Then construct to check whether the cell that is being edited is in column A. If this is the case, then it inserts the timestamp in the adjacent cell in column B. Note that this code would overwrite any existing contents of the cells in column B. If you want. You can modify the code to add a message box to show a prompt in case there is any existing content. Where to Put this Code? This code needs to be entered as the worksheet change event so that it gets triggered whenever there is a change. To do this: Make sure you save the file with .XLS or .XLSM extension as it contains a macro. Creating a Custom Function to Insert TimestampCreating a custom function is a really smart way of inserting a timestamp in Excel. It combines the power of VBA with functions, and you can use it like any other worksheet function. Here is the code that will create a custom “Timestamp” function in Excel: 'Code by Sumit Bansal from http://trumpexcel.com Function Timestamp(Reference As Range) If Reference.Value <> "" Then Timestamp = Format(Now, "dd-mm-yyy hh:mm:ss") Else Timestamp = "" End If End Function Where to Put this Code? This code needs to be placed in a module in the VB Editor. Once you do that, the Timestamp function becomes available in the worksheet (just like any other regular function). Here are the steps to place this code in a module: Now you can use the function in the worksheet. It will evaluate the cell to its left and insert the timestamp accordingly. It also updates the timestamp whenever the entry is updated. Make sure you save the file with .XLS or .XLSM extension as it contains VB code. Hope you’ve found this tutorial useful. Let me know your thoughts in the comments section. You May Also Like the Following Excel Tutorials and Resources:
Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster61 thoughts on “How to Quickly Insert Date and Timestamp in Excel”
Comments are closed. When you change a name Excel automatically updates the blank in any formulas that reference it?Excel includes a special group of functions to modify text. When you change a name, Excel automatically updates the name in any formula that references that name. To move or copy more than one non-consecutive worksheet, press _______ on the keyboard and click the worksheets you want to copy or move.
What is automatically updated if you change data in the worksheet?The formatting is updated if you change any data in the worksheet.
Are the names you created and names Excel creates automatically?Once you've created a named range, it will appear automatically in formulas when you type the first letter of the name. Press the tab key to enter the name when you have a match and want Excel to enter the name.
Does Excel automatically adjust cell references to reflect new formula locations?c. Excel automatically adjusts cell references to reflect new formula locations.
|