How to Preserve Static Dates in Excel Without Recalculating TODAY()
Question details
The user needs to permanently record the exact date a cell status changes, avoiding the dynamic recalculation inherent to the TODAY() function.

- Product
- Excel
- Device & OS
- Windows / macOS
- Scenario
- Tracking project or task status changes (e.g., changing a cell to "In progress" or "Done") and needing an exact, permanent date timestamp to log when the event occurred.
- Observed behavior
- When using the TODAY() formula to record a date, the value recalculates and updates to the current system date every time the workbook is opened or recalculated, losing the original timestamp.
Ensure you are using the desktop version of Excel for Windows or macOS, as VBA macros are required for this automation and are not supported in Excel for the Web.
Use VBA to Automatically Insert a Static Date
Utilize a Worksheet_Change event in VBA to write the current system date into a specific cell whenever a target status changes, ensuring the date remains permanent.
Because formula functions like TODAY() and NOW() are volatile and constantly update, Visual Basic for Applications (VBA) is the most reliable way to create a static timestamp based on a specific condition, such as a dropdown status change.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in your Excel desktop application.
In the 'Project - VBAProject' panel on the left, double-click the specific worksheet name (e.g., Sheet1) where you want the automatic timestamps to appear.
Paste a Worksheet_Change macro. Configure your logic so that if a cell in your target range (e.g., G4:G1000) changes to 'In progress', you write 'Date' to the adjacent cell using the Offset(0, 1) method. If it changes to 'Done', use Offset(0, 2) to write the date two columns to the right.
Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown. This ensures your VBA code is saved and runs properly in the future.
Manually Insert a Static Date using Keyboard Shortcuts
If you prefer not to use VBA or only need to log dates occasionally, you can quickly insert a static date manually that will never recalculate.
Create Macros and Static Timestamps Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to automate status timestamps seamlessly. It offers a highly familiar interface that is fully compatible with Microsoft Excel's .xlsm format, making it the perfect tool for dynamic data entry.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your existing tracking workbook or create a new one.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon to access the built-in VBA Editor.
- 3. Insert the Timestamp Code: Double-click your active worksheet in the editor and paste your Worksheet_Change event code to automate the static dates.
- 4. Save as .xlsm: Save your document as an Excel Macro-Enabled Workbook (.xlsm) to keep your event triggers active and compatible.

Frequently Asked Questions
Why does the TODAY() function keep changing my recorded dates?
The TODAY() function is designed to be volatile, meaning it automatically recalculates and updates to the current system date whenever you open the workbook or make any changes to it. It cannot be used for static historical timestamps.
Can I use VBA macros in Excel for the web?
No, VBA macros are currently not supported in the web version of Excel. You must use the installed desktop application for Windows or macOS to execute and edit VBA scripts.
How do I insert a static time along with the date manually?
To insert a static time manually, select a cell and press 'Ctrl' + 'Shift' + ':' (colon). To insert both date and time, press 'Ctrl' + ';' for the date, press Space, and then press 'Ctrl' + 'Shift' + ':' for the time.
Will my VBA macro still work if I save my file as a standard .xlsx file?
No, standard .xlsx files strictly forbid macros and will strip out your VBA code upon saving. You must save the file as an Excel Macro-Enabled Workbook (.xlsm) to preserve your automation.




