Calculation Issues

Keep a Date Fixed When a Cell Changes in Excel

Steve KSteve K Nov 18, 2026 869 views

Question details

Create a date that is recorded once when another cell is populated and does not update afterward.

Keep a Date Fixed When a Cell Changes in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet needs a fixed date associated with an entry in another cell.
Observed behavior
A circular formula using TODAY depends on iterative calculation, which may be disabled or reset when the workbook is reopened.
Before you start

Choose the cell that will receive the date and the cell that will trigger it. For the VBA example below, the date goes in A2 and the triggering entry goes in B2.

Solution 1Recommended

Enter a fixed date manually in Excel

Use a keyboard shortcut when you enter the triggering value; no formula or iterative calculation is required.

1
Enter the triggering value

Type the desired value in B2 and press Enter.

2
Insert the date

Select A2 and press Ctrl+; to insert the current date as a fixed value.

3
Format the date if needed

With A2 selected, use Home > Number Format to choose a date display.

Enter a fixed date manually in Excel
Manual entry required: The shortcut inserts the date only when you press it. Unlike TODAY(), the resulting value does not update automatically.
Fixed dates in WPS Spreadsheets

Enter a fixed date in WPS Spreadsheets

Use WPS Spreadsheets to enter a date as a value alongside your worksheet entry, without relying on TODAY or iterative calculation.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Enter your value: Type the triggering value in the appropriate cell, such as B2.
  3. 3. Add the fixed date: Select the date cell, such as A2, and press Ctrl+; to insert today's date. This is a manual entry, not an automatic trigger.
  4. 4. Save the workbook: Choose Menu > Save or Save As to retain the entry in your workbook.
Insert a date that stays unchanged after entryOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formatting toolsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does TODAY() change when I reopen my workbook?

TODAY() is a formula that returns the current date when Excel recalculates. Enter a date as a value with Ctrl+; if it must remain fixed.

Can Excel add the date automatically when I type in another cell?

Yes. In desktop Excel, add a Worksheet_Change VBA event through the worksheet tab's View Code option, then save the file as an .xlsm workbook and enable trusted macros.

Will the VBA date change if I edit the triggering cell again?

Not with the example above. It writes to A2 only when A2 is empty, so subsequent edits to B2 leave the recorded date in place.

Where can I enable iterative calculation for an existing circular formula?

In Excel, go to File > Options > Formulas and enable iterative calculation. Save the workbook afterward. A fixed value or worksheet event avoids relying on that setting.