The TODAY function in Excel is designed to show the current date, but sometimes it stops updating. This happens when the workbook’s calculation mode is set to manual. This article explains how to switch Excel back to automatic calculation so the TODAY function works correctly.
Key Takeaways: Fixing the TODAY Function
- Formulas > Calculation Options > Automatic: This is the primary setting that forces Excel to update all formulas, including TODAY, when you open a file or change data.
- F9 key: Manually triggers a full calculation of all formulas in all open workbooks as a temporary fix.
- File > Options > Formulas > Workbook Calculation: The central location to permanently set the calculation mode for Excel.
Why the TODAY Function Stops Showing the Current Date
The TODAY function returns the system date from your computer’s clock. Unlike static text, it is a volatile function that should recalculate each time the workbook is opened or a calculation occurs. The most common reason it appears frozen is because the workbook’s calculation mode is set to Manual. In this mode, Excel only calculates formulas when you specifically command it to, such as by pressing F9. This setting can be applied to a single workbook or to the entire Excel application, often changed accidentally or to improve performance with very large files.
Steps to Enable Automatic Recalculation
Follow these steps to change the calculation setting and ensure the TODAY function updates automatically.
- Check the Calculation Status Bar
Look at the status bar at the bottom of your Excel window. If you see the word “Calculate,” it means manual calculation is active and your formulas are out of date. - Open the Calculation Options Menu
Go to the Formulas tab on the ribbon. In the Calculation group, click the Calculation Options button. - Select Automatic
From the dropdown menu, select “Automatic.” The status bar message will disappear, and Excel will immediately recalculate all formulas, updating the TODAY function. - Force an Immediate Update
After switching to Automatic, press the F9 key. This performs a calculation now and confirms the setting is working. The date in your TODAY formula should refresh. - Save the Workbook
Save your Excel file. The Automatic calculation setting is stored with the workbook, so it will use this mode the next time you open it.
Using Excel Options for a Permanent Setting
If the problem affects all your workbooks, change the default application setting.
- Open Excel Options
Click File > Options to open the Excel Options dialog box. - Navigate to Formulas Settings
In the left pane, select the “Formulas” category. - Set Workbook Calculation
Under the “Calculation options” section, ensure the option for “Workbook Calculation” is set to “Automatic.” Click OK to apply the change.
If the TODAY Function Still Does Not Update
After enabling automatic calculation, the date should update. If it does not, one of these other issues may be the cause.
Excel Shows an Old Date After Opening the File
If the date is wrong only when you first open the file, the workbook may have been saved with manual calculation on. Excel will not recalculate until you perform an action. Simply pressing Enter in any cell or pressing F9 will trigger the update. To prevent this, always save your files with the calculation mode set to Automatic.
The Cell is Formatted as Text
A cell formatted as Text will display a formula as plain text instead of calculating it. If your TODAY function shows as =TODAY() instead of a date, change the cell format. Select the cell, go to the Home tab, and in the Number group, choose “General” or “Date” from the dropdown. Then press F2 and Enter to re-enter the formula.
Excel Add-ins Interfere with Calculation
A third-party add-in might override Excel’s calculation engine. To test this, start Excel in Safe Mode. Hold the Ctrl key while clicking the Excel icon to start it. If TODAY works in Safe Mode, an add-in is likely causing the problem. Disable add-ins one by one via File > Options > Add-ins to find the culprit.
Manual vs Automatic Calculation: Key Differences
| Item | Automatic Calculation | Manual Calculation |
|---|---|---|
| Default Setting | Yes, for most new workbooks | No, must be manually selected |
| Formula Update Trigger | Any change to the workbook | Only when user presses F9 or similar |
| Performance Impact | Can slow down large, complex workbooks | Faster data entry in large files |
| Status Bar Indicator | Shows nothing | Displays the word “Calculate” |
| Effect on TODAY/NOW | Updates daily on file open | Remains static until manual recalculation |
You can now ensure the TODAY function always shows the correct date by keeping Excel in Automatic calculation mode. For large models where performance is slow, consider using the Manual setting but remember to press Shift+F9 to recalculate just the active worksheet. An advanced tip is to use the NOW function if you also need the current time, but note it updates even more frequently than TODAY.