Calculation Issues

How to Stop Excel TODAY Function from Changing After Task Completion

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to prevent a task completion date from continuously updating when using the TODAY function in Excel.

Product: Excel
Device & OS: not provided
Scenario: Recording a fixed, permanent completion date when a task status changes to complete.
Observed behavior: Because the TODAY() function recalculates automatically, the date changes every single day, failing to keep an accurate historical record of when the task was actually completed.

Before you start

If you choose to use the VBA macro solution, ensure you save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to prevent losing your script when the file is closed.

Solution 1Recommended

Use a Keyboard Shortcut for Manual Static Date Entry

The easiest way to enter a fixed date that will not update automatically is to use a keyboard shortcut instead of typing the TODAY() formula.

Select the target cell

Click on the cell in your completion-date column where you want the final date to be recorded.

Press the date shortcut

Press the Ctrl + ; (semicolon) keys simultaneously on your keyboard. This instantly inserts the current date.

Confirm the entry

Press the Enter key. The date is now permanently typed into the cell as a static value and will not change tomorrow.

Solution 2

Create a VBA Macro for Automatic Date Stamping

For automated tracking, you can use a Worksheet_Change event in VBA that watches your status column and stamps a fixed date when a task is marked as 'DONE'.

Manage Tasks Effectively with WPS Office

Easily Track Static Task Completion Dates in WPS Spreadsheet

WPS Office offers a highly capable Spreadsheet application that fully supports all standard Excel shortcuts, formulas, and VBA macros. Managing static completion dates for your projects is seamless, whether you prefer manual shortcuts or automated scripts.

1. Install WPS Office: Download and install the free WPS Office suite on your device.
2. Open your task tracker: Launch WPS Spreadsheet and open your existing project management or task tracker file.
3. Insert static dates instantly: Select any cell and press Ctrl+; to instantly log a permanent completion date without using volatile formulas.
4. Automate with macros: If you require automation, press Alt+F11 to open the built-in VBA editor and paste your Worksheet_Change event script.

100% compatibility with Microsoft Excel formats (.xlsx, .xlsm)Native support for static date and time keyboard shortcuts (Ctrl+;)Built-in advanced VBA macro support for automated date stampingLightweight, lightning-fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why does the TODAY() function change every day in Excel?

The TODAY() function is considered a volatile function in Excel. This means it is designed to always query the system clock and display the current date whenever the workbook is opened or recalculated, making it unsuitable for logging historical completion dates.

Can I convert an existing TODAY() formula into a static date?

Yes. Select the cell that contains the TODAY() formula, press Ctrl+C to copy it, then right-click on the same cell and select 'Paste as Values' (the clipboard icon with the 123). This removes the underlying formula and leaves just the fixed date.

How can I quickly insert a static time along with the date?

To insert a static time, press Ctrl+Shift+; on your keyboard. If you need both the date and time in the same cell, press Ctrl+; to insert the date, press the Spacebar to add a space, and then press Ctrl+Shift+; to insert the time.

Excel & Data Problems