Excel’s NOW and TODAY functions update automatically, which can be a problem when you need a fixed record. The functions recalculate every time the worksheet recalculates, changing the displayed date or time. This article explains how to stop this behavior by converting the dynamic results into permanent, static values. You will learn two reliable methods to lock in today’s date or the current timestamp.
Key Takeaways: Locking Dynamic Dates in Excel
- Paste Special > Values: This method replaces a formula with its current result, permanently removing the link to the NOW or TODAY function.
- Copy and Paste as a Static Value: A quick alternative using the right-click menu or keyboard shortcuts to paste only the value from a formula cell.
- F2 then F9 in the Formula Bar: This keyboard technique evaluates and permanently replaces a single formula with its calculated result directly in the cell.
Understanding Excel’s Volatile Date Functions
The NOW and TODAY functions are known as volatile functions. A volatile function recalculates whenever any change is made to the workbook, not just when its direct inputs change. The NOW function returns the current date and time, while TODAY returns only the date. Both draw this information from your computer’s system clock.
This automatic update is useful for dashboards or reports that must always show the current moment. However, it becomes a drawback when you need to record a specific date, like an invoice date or a project milestone. If you save and reopen the file tomorrow, the cell will display tomorrow’s date, losing the original record. Converting the formula to a static value solves this by breaking the link to the function and preserving the result at the moment of conversion.
Steps to Convert NOW and TODAY to Static Values
You can convert a dynamic date to a static value using paste operations or a direct keyboard shortcut. The following methods work for single cells, ranges, or entire columns.
Method 1: Use Paste Special to Replace Formulas with Values
- Select the cells with formulas
Click on the cell or drag to select a range containing the NOW() or TODAY() formulas you want to lock. - Copy the selected cells
Press Ctrl+C on your keyboard or right-click the selection and choose Copy from the context menu. - Open the Paste Special dialog
Keep the cells selected. Right-click on the same selected range and hover over Paste Special, then click on Paste Special again from the fly-out menu. Alternatively, go to the Home tab on the ribbon, click the small arrow under the Paste button, and select Paste Special. - Select the Values option
In the Paste Special dialog box, under the Paste section, select the radio button for Values. Click OK. - Verify the conversion
The cells will now display the same date or timestamp, but clicking on one will show a static value in the formula bar instead of the =NOW() or =TODAY() formula.
Method 2: Use a Keyboard Shortcut for a Quick Paste
- Copy the formula cells
Select the cells with the dynamic date formulas and press Ctrl+C to copy them. - Paste the values directly
With the destination still selected, press Alt, then H, then V, then V on your keyboard. This is the ribbon shortcut sequence for Home > Paste > Values. The formulas will be replaced with their static results immediately.
Method 3: Convert a Single Formula in the Formula Bar
- Select the cell and enter edit mode
Click on the cell containing the =NOW() or =TODAY() formula. Then, click into the formula bar at the top of the Excel window or press F2 on your keyboard. This puts the cell into edit mode. - Evaluate the formula to a value
With the entire formula highlighted in the formula bar, press the F9 key. This will calculate the formula and replace the text =NOW() with its current serial number value, like 45321.625 for a date and time. - Commit the static value
Press Enter on your keyboard. The cell will now display the date or time, and the formula bar will show the serial number value, not the original function.
Common Mistakes and Limitations to Avoid
When converting dynamic dates to static values, users often encounter a few specific issues. Knowing these ahead of time helps prevent data loss or confusion.
Static Values Do Not Update for New Rows
If you convert a column of TODAY() formulas to values and then add a new row, the new cell will be empty. The paste operation only affects existing cells. For new rows, you must either enter a static date manually or use a different method, like a worksheet event macro, which is beyond basic conversion.
Losing the Original Dynamic Formula
The conversion process is irreversible through standard Undo after you perform certain other actions. It is a good practice to make a copy of your worksheet or the specific column before converting formulas to values. This preserves the original dynamic formulas in case you need them later for other calculations.
Date Formatting May Appear to Change
Excel stores dates as serial numbers. When you paste a value, it retains the cell’s number format. If your cell was formatted as a date, it will still look like a date. However, if you paste the value into a cell with General format, it may display as a number like 45321. Always verify the cell format is Date or Time after pasting values.
Paste Special vs. Keyboard Shortcut vs. Formula Bar Conversion
| Item | Paste Special > Values | Keyboard Shortcut (Alt+H+V+V) | F2 then F9 in Formula Bar |
|---|---|---|---|
| Best for | Converting large ranges or entire columns | Quick conversion after copying cells | Converting a single formula cell precisely |
| Speed | Medium, requires dialog box navigation | Fast, uses ribbon shortcuts | Fast for one cell, slow for many |
| Reversibility | Can be undone immediately with Ctrl+Z | Can be undone immediately with Ctrl+Z | Can be undone immediately with Ctrl+Z |
| Result | Replaces formula with its value in the same cell location | Replaces formula with its value in the same cell location | Replaces formula text with its calculated serial number value |
You can now create permanent records of dates and times in your Excel workbooks. Use the Paste Special method for bulk conversions on multiple cells. Remember that these static values will not refresh, which is ideal for timestamps on invoices or project logs. For a related technique, explore using Ctrl+; to insert a static date directly without a formula. An advanced tip is to use the Text to Columns wizard on a date column with a fixed width of zero as another quick way to force formulas to values.