Excel Insert Current Date: Static or Auto-Updating
To insert the current date in Excel, use the keyboard shortcut Ctrl+; for a static date that never changes, or the formula =TODAY() for a date that updates each time the workbook recalculates. You can also combine them with Ctrl+Shift+; for the current time, or use =NOW() for a dynamic timestamp. This guide explains each method, when to use them, and how to avoid common pitfalls.
Why Excel Offers Two Different Current Dates
Excel treats dates as serial numbers, starting from January 1, 1900 (serial 1). The TODAY function returns the serial number for the current date, formatted as a date. Because it is a volatile function, it recalculates whenever Excel refreshes — which happens when you open the file, change any cell, or press F9. A static date, on the other hand, is simply a typed value that Excel stores as a number; it does not change unless you edit it. Understanding this difference is key: do you want a record of when you entered data, or a live clock that always shows “today�?
How to Insert a Static Date (Shortcut)
The quickest way to insert a fixed date is the keyboard shortcut Ctrl+; (semicolon). This enters the current date based on your computer’s system clock, and it behaves like any other typed value — it will not change tomorrow. For a static time, use Ctrl+Shift+;. If you need both date and time as a static value, press Ctrl+;, then a space, then Ctrl+Shift+;. These shortcuts work in Excel for Windows and Mac (on Mac, the same keys apply, though some keyboards may require the Fn key).
How to Insert a Dynamic Date (TODAY and NOW)
To have the date update automatically, type =TODAY() in a cell and press Enter. The cell will display the current date, and it will refresh each time the worksheet recalculates. Similarly, =NOW() returns both the current date and time. These are volatile functions, meaning they recalculate whenever any change occurs in the workbook, or when you open it. If you want the date to update only when you open the file, but not continuously, you can set calculation to manual (Formulas tab > Calculation Options > Manual) and press F9 when you want a refresh.
Choosing Between Static and Dynamic
Your choice depends on the purpose:
- Static date (Ctrl+;) – Use for logs, invoices, or records where you need to preserve the exact date of entry. It remains unchanged, which is essential for audit trails.
- Dynamic date (TODAY()) – Use for dashboards, age calculations, or countdowns where you always want the current date. For example, to calculate days until a deadline, use =A1-TODAY().
- Dynamic time (NOW()) – Use for timestamps that reflect the last calculation time, but note that it changes frequently.
A common misconception is that TODAY() updates every second. It does not — it updates only when Excel recalculates, which happens on file open, on cell edits, or when you force a recalculation. It does not tick in real time unless you enable iterative calculation or use VBA.
Common Problems and How to Fix Them
Problem: The date shows as a number like 45292. This means the cell is formatted as General or Number. Select the cell, press Ctrl+1, go to the Number tab, and choose a Date format. Problem: The date is wrong (e.g., shows 1/0/1900). This usually happens if the cell contains a formula that returns 0 or if the system date is incorrect. Check your computer’s clock. Problem: TODAY() does not update. If calculation is set to Manual, it won’t. Press F9 to recalculate, or change to Automatic. Problem: The static date changes after reopening. It shouldn’t, unless you accidentally used a formula. Check the formula bar — if it shows =TODAY(), it’s dynamic, not static.
Formatting the Date and Time
After inserting, you may want to change how it displays. Right-click the cell, choose Format Cells, then pick a date format like “14-Mar-2021� or “March 14, 2021�. For time, choose a time format. You can also create custom formats, e.g., dd-mm-yyyy hh:mm. Note that formatting does not change the underlying serial number; it only changes the display. This is useful for reports where you want a specific style.
Alternatives and Advanced Tips
If you need a timestamp that updates every time a cell changes, you can use a circular formula with iterative calculation enabled, but that is complex and error-prone. A simpler approach is to use a macro (VBA) that writes the date when a cell is edited — but that requires enabling macros. For most users, the built-in shortcuts and functions are sufficient. Another tip: to insert today’s date in multiple cells at once, select the range, type Ctrl+; and press Ctrl+Enter. This fills all selected cells with the same static date.
Frequently Asked Questions
Q: How do I insert the current date in Excel that doesn’t change? Press Ctrl+; (semicolon). This enters a static date as a value. It will not update when you reopen the file.
Q: How do I make a cell always show today’s date? Use the formula =TODAY(). It will update each time the workbook recalculates. For date and time, use =NOW().
Q: Why is my =TODAY() formula showing a number? The cell is probably formatted as General. Change the cell format to a date format via Format Cells > Number > Date.
Q: Can I insert the current date without a formula? Yes, use the keyboard shortcut Ctrl+;. It inserts a static value, not a formula.
Q: What is the difference between TODAY() and NOW()? TODAY() returns only the current date, while NOW() returns both date and time. Both are volatile, but NOW() updates more often because it includes time.
Q: How do I insert the current date in Excel on a Mac? The shortcut is also Ctrl+; (or Cmd+; on some Mac keyboards). If it doesn’t work, try Cmd+; or check your system settings.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.