VBA & Macro Problems

How to Enter a Permanent Date When an Excel Formula Returns TRUE

Maira Mehtab Oct 20, 2026 869 views

Question details

The user wants to replace a formula result of TRUE with a static, permanent date in Excel, as standard date formulas update dynamically.

How to Enter a Permanent Date When an Excel Formula Returns TRUE

Product: Excel
Device & OS: not provided
Scenario: Tracking task completion dates or triggering a permanent date stamp when specific conditions are met in a spreadsheet.
Observed behavior: Standard Excel formulas like TODAY() update automatically, preventing the retention of the original date when the condition was first met.

Before you start

Before proceeding, ensure you have saved a backup copy of your workbook, as running VBA macros permanently alters data and cannot be undone using the standard Undo feature.

Solution 1Recommended

Use a VBA Macro to Replace TRUE with a Static Date

This is the most reliable method. It converts your dynamic formulas into static values and replaces the TRUE results with the current system date using VBA.

A standard formula cannot permanently replace its own result with a hard-coded date. By using a VBA macro, you can freeze the formula results and inject today's date where the condition is met.

Select the target cells

Highlight the range or column containing the formulas that currently evaluate to TRUE or FALSE.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications window, then click Insert > Module.

Enter the Macro Code

Create a macro that converts the selection to values and replaces TRUE with a date. Use the code snippet: Selection.Value = Selection.Value followed by Selection.Replace What:="TRUE", Replacement:=Date.

Run the Macro

Close the VBA editor and press ALT + F8 to open the Macro dialog. Select your newly created macro and click Run to instantly stamp the dates.

Use a VBA Macro to Replace TRUE with a Static Date

VBA Date Function: Using the 'Date' function in VBA is better than typing a fixed date string, as it automatically pulls the exact current date from your system when the macro is executed.

Solution 2

Use Iterative Calculation with a Circular Reference Formula

This method relies on changing Excel's calculation settings to allow circular references, capturing the date without needing VBA.

Advanced Spreadsheet Editor

Easily Manage Macros and Formulas in WPS Spreadsheet

WPS Office fully supports advanced Excel functions, iterative calculations, and VBA macros, allowing you to seamlessly create static timestamps and automate repetitive tasks without compatibility issues.

1. Open WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook containing your conditional formulas.
2. Access Developer Tools: Navigate to the Developer tab on the top ribbon. If you do not see it, you can enable it in the WPS Options menu.
3. Insert the VBA Code: Click on 'Macros' or press ALT + F11, insert a new module, and paste your TRUE-replacement VBA code.
4. Execute the Date Stamp: Select your cells, run the macro, and instantly convert your dynamic conditions into permanent date values.

Fully compatible with Microsoft Excel file formats (.xlsx, .xlsm)Built-in support for VBA macros and developer toolsIntuitive settings for managing iterative calculationsLightweight, fast, and free to use for daily tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does the TODAY() function change every day?

The TODAY() function is volatile, meaning it recalculates every time the worksheet is opened, changed, or refreshed. It is designed to always display the current system date, which is why it cannot act as a static timestamp on its own.

How do I manually insert a static date without using formulas or macros?

You can quickly insert the current date into any selected cell by pressing the keyboard shortcut Ctrl + ; (semicolon). This hard-codes the date into the cell permanently.

Will disabling iterative calculations break my formula timestamps?

Yes. If you disable iterative calculation later, Excel will return a Circular Reference warning for the cell, and the formula will fail to retain the static date correctly.

Excel & Data Problems