Add a date and time stamp.
The =NOW() formula, for example, returns the date and time of the most recent worksheet calculation. You also might see =NOW()-Invoice Date to calculate the age of an invoice. Because this function returns both a date and a time, however, the invoice's age will change once the current time passes 12 p.m.
To avoid that, use =TODAY() instead, which returns the current date without adding any hours throughout the day. This makes it more appropriate for calculating the number of days between the invoice date and today. Use =MOD(NOW or =NOW()-TODAY() if you need to return the current time of day.
Recording the Time or Date an Event Happens
The NOW and TODAY functions are volatile, which means they aren't appropriate if you need to capture the time that something happens and have that value stay constant in the future. They don't remember their initial value and constantly update as you change other cells in the spreadsheet.
Excel provides two shortcut keys to record the current date or time. The shortcut that inserts the time into a cell is easy to remember because it uses a colon, just like how you write out time (for example, 9:25). Pressing Ctrl+Shift+: inserts the current time in the active cell. Inserting the date is very similar. The only difference is that it doesn't involve the Shift key. Pressing Ctrl+; inserts the current date as a static value.
Paste Value as a Workaround
Oddly, Excel doesn't have an easy way to insert the current date and time together. One solution is to use =NOW() and then immediately convert the formula to values. The shortcut for this involves using Ctrl+C to copy the cell and then Alt+ESV<Enter> to paste the cell value. That is, hold down the Alt key and press E, S, V. Then release Alt and press Enter. If you have Excel 2010 or newer and your keyboard includes an Application key, you can also press Ctrl+C, Application, V. The Application key is often to the right of the spacebar, between the Alt and Ctrl keys. It often looks like a mouse cursor pointing at a list or dropdown menu.
F9 as a Workaround
Using F9 by itself tells Excel to calculate the formulas in the worksheet. But if you are in the process of typing a formula, hitting F9 will evaluate the formula in the formula bar and replace the cell contents with the result of the formula (instead of adding the formula itself). Most people use this as a great way to test a formula, and they will immediately undo the evaluation to go back to the original formula. But for our purpose, we want to keep it. Type =NOW(), press F9, and then Enter. This inserts a static date and time in the active cell.
Adding a Macro for a True Date and Time Stamp
You also can record a macro in your personal macro workbook that will allow you to quickly add a date and time stamp. Recording the macro takes only a few minutes. Follow these steps:
1. Choose a blank cell in the worksheet.
2. From the View tab of the ribbon, open the Macros dropdown and choose Record Macro.
3. In the Record Macro dialog, type a macro name, such as DateTimeStamp. Remember not to use any spaces in the macro name.
4. In the Shortcut key box, press Shift+T to assign the macro to Ctrl+Shift+T. (It seems strange that you don't have to type Ctrl+Shift+T in this box, but Excel already assumes Ctrl will be a part of the shortcut.)
5. In the Store Macro In dropdown, choose Personal Macro Workbook. Items stored in the personal macro workbook will be available for use in every workbook that you open on the same computer.
6. You can enter a description of the macro, but that's optional.
7. Click OK to start recording (see Figure 1).
8. In the current cell, type =NOW().
9. Press Ctrl+Enter to accept the formula without moving to a new cell.
10. Press Ctrl+C to copy.
11. Paste Values (either Alt+ESV<Enter> or Application, V) to lock the formula to the current value.
12. On the View tab, go to Macros and select Stop Recording.
When you exit Excel, you will see a one-time message that asks if you want to save the Personal Macro Workbook. Click Yes. To test the macro, select a new cell and press Ctrl+Shift+T. The current date and time should appear in the cell as a static value.
Bill Jelen is the host of MrExcel.com and a frequent speaker at professional development events for IMA[R] chapters. Send questions for future articles to IMA@MrExcel.com
|Printer friendly Cite/link Email Feedback|
|Title Annotation:||TECHNOLOGY: EXCEL|
|Date:||Jun 1, 2014|
|Previous Article:||Do consulting services threaten audit performance?|
|Next Article:||Younger members earning more.|