VBA & Macro Problems

How to Preserve Static Dates in Excel Without Recalculating TODAY()

Natalie TaylorNatalie Taylor Nov 4, 2026 870 views

Question details

The user needs to permanently record the exact date a cell status changes, avoiding the dynamic recalculation inherent to the TODAY() function.

How to Preserve Static Dates in Excel Without Recalculating TODAY()
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in your Excel desktop application.

2
Access the Worksheet Module

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.

3
Insert the VBA Code

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.

4
Save as a Macro-Enabled Workbook

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.

Macro Security: You must enable macros when reopening the .xlsm file; otherwise, the automatic timestamps will not trigger upon status changes.
Advanced Spreadsheet Automation

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. 1. Open WPS Spreadsheet: Launch WPS Office and open your existing tracking workbook or create a new one.
  2. 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon to access the built-in VBA Editor.
  3. 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. 4. Save as .xlsm: Save your document as an Excel Macro-Enabled Workbook (.xlsm) to keep your event triggers active and compatible.
Fully supports VBA and macro-enabled (.xlsm) workbooksHighly compatible with Microsoft Office Excel formatsFree and lightweight alternative for daily data processingBuilt-in keyboard shortcuts for quick static data entry
microsoft office alternative - wps office

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.