Calculation Issues

How to Keep a Fixed Date When an Excel Cell Reaches Zero

Steve K Sep 27, 2026 869 views

Question details

The user wants to generate and keep a permanent date in a cell when another cell's value drops to zero, without the date updating automatically upon recalculation.

How to Keep a Fixed Date When an Excel Cell Reaches Zero

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking metrics like inventory or countdowns where an exact, unchanging timestamp must be recorded the moment a specific cell's value hits zero.
Observed behavior: Standard date functions like TODAY() and NOW() update continuously whenever the worksheet recalculates, erasing the original date the cell hit zero.

Before you start

Decide if you want a fully automated solution (which requires changing calculation settings or using VBA) or a manual approach. Standard formula functions will always recalculate unless you intentionally lock them.

Solution 1Recommended

Use Iterative Calculation to Freeze the TODAY() Function

By enabling iterative calculations, you can create a circular formula that locks in the current date the first time the cell reaches zero, preventing future updates.

Excel normally throws an error if a formula refers to its own cell (a circular reference). However, by enabling Iterative Calculation, you can use this behavior to your advantage to freeze a value in place.

Enable Iterative Calculation

Go to File > Options > Formulas. Check the box for 'Enable iterative calculation' and click OK.

Input the Circular Formula

Assuming your target value is in cell A2 and you want the timestamp in B2, click on cell B2 and enter the formula: =IF(A2=0, IF(B2="", TODAY(), B2), "").

Format as Date

Select cell B2, right-click, choose 'Format Cells', and select your preferred Date format. The date will now permanently lock the moment A2 becomes zero.

Use Iterative Calculation to Freeze the TODAY() Function

Formula Explanation: This formula checks if A2 is zero. If true, it checks if B2 is empty; if empty, it inserts TODAY(). If B2 already has a date, it simply returns its own value (B2), preventing the date from changing tomorrow.

Solution 2

Record a Permanent Timestamp Using VBA

A VBA script runs in the background and automatically inserts a static, hardcoded date value the moment a specific cell reaches zero.

Solution 3

Use Keyboard Shortcuts for Static Dates

The simplest and most foolproof way to enter a permanent date is by using Excel's built-in keyboard shortcut once you notice the value reach zero.

Efficient Spreadsheet Management

Automate Timestamps Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports Iterative Calculation and VBA macros, allowing you to easily set up static timestamps when inventory or tracked values hit zero. It operates exactly like Microsoft Excel but with a lighter and faster interface.

1. Enable Iterative Calculation in WPS: Open WPS Spreadsheet, click on the 'Menu' button at the top left, select 'Options', navigate to the 'Calculation' tab, and check 'Enable iterative calculation'.
2. Enter the Freezing Formula: In your target timestamp cell, type =IF(A2=0, IF(B2="", TODAY(), B2), "") and press Enter.
3. Utilize VBA if Preferred: Go to the 'Developer' tab, click 'Visual Basic Editor', and paste your Worksheet_Change event code just as you would in standard Excel.

100% compatible with Microsoft Excel formulas, formatting, and VBA macros.Easily toggle Iterative Calculation to build circular logic for fixed timestamps.Free to use, lightweight, and fast to launch on Windows, Mac, and Linux.Tabbed interface allows you to manage multiple workbooks effortlessly.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the NOW() or TODAY() function keep changing to the current date?

NOW() and TODAY() are volatile functions. This means they automatically recalculate and update to the current system date and time whenever any change is made to the workbook or when the workbook is opened.

Can I lock a formula result without using VBA or Iterative Calculation?

Yes, but it requires a manual step. You can let the TODAY() formula generate the date, then copy that cell and use 'Paste Special' > 'Values' over the same cell. This removes the formula and leaves only the static text date.

Will the Iterative Calculation method slow down my workbook?

In most normal use cases, having 'Enable iterative calculation' checked with a low 'Maximum Iterations' setting (like 1 or 100) will not noticeably slow down your workbook, unless you have thousands of complex circular formulas.

Excel & Data Problems