VBA & Macro Problems

How to Permanently Save a Static Date in Excel Using VBA

Kushani Nimanthika Sep 25, 2026 869 views

Question details

The user needs to display a date in a worksheet when a specific day arrives and preserve that date permanently, preventing it from recalculating when the condition changes.

How to Permanently Save a Static Date in Excel Using VBA

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Automating a spreadsheet to log a specific date permanently once a time-based condition is met.
Observed behavior: Standard Excel formulas recalculate automatically, making it impossible to permanently preserve a date based on a temporary condition without using a macro.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) so that your VBA code is retained after closing the file, and that macros are enabled in your security settings.

Solution 1Recommended

Use a VBA Workbook_Open Event to Insert a Static Date

Since standard Excel formulas recalculate automatically, you must use a VBA macro triggered when the workbook opens to write a hardcoded date into your target cell.

Standard functions like TODAY() or IF() cannot permanently freeze a value once their condition evaluates to false. A VBA script bypasses this by writing the date directly as a static value when a condition is met during the file's opening process.

Open the VBA Editor

Press the ALT + F11 keys on your keyboard while in your spreadsheet to open the Visual Basic for Applications (VBA) editor.

Access the ThisWorkbook module

In the Project Explorer pane on the left side of the VBA editor, double-click on 'ThisWorkbook' to open its code window.

Insert the VBA code

Copy and paste the following macro code into the window: Private Sub Workbook_Open() If Date = DateSerial(2024, 11, 22) Then Worksheets("Sheet1").Range("A2").Value = Date End If End Sub

Customize the date and cell references

Modify the DateSerial(2024, 11, 22) values to match your target date condition. Change 'Sheet1' and 'A2' to your actual worksheet name and destination cell where the static date should appear.

Save and test your workbook

Close the VBA editor and save your file. Select 'Excel Macro-Enabled Workbook (*.xlsm)' from the 'Save as type' dropdown menu. Close and reopen the file to trigger the macro.

Use a VBA Workbook_Open Event to Insert a Static Date

Enable Macros: You will need to click 'Enable Content' when the security warning appears upon opening the workbook for the Workbook_Open event to run automatically.

WPS Spreadsheet Automation

Automate Date Logging and Macros with WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros, allowing you to seamlessly automate tasks like inserting permanent dates. It offers a familiar interface and high compatibility with Excel macro-enabled files.

1. Download and install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
2. Open your macro-enabled file: Launch WPS Spreadsheet and open your .xlsm file containing the Workbook_Open event.
3. Manage macros from the Developer tab: Navigate to the Developer tab to edit your VBA code, run macros, and automate your workflow exactly as you would in Excel.

Fully compatible with Microsoft Excel .xlsm formats and VBA scripts.Lightweight and fast performance for handling complex macros.Familiar user interface makes transitioning easy.Free to use with powerful spreadsheet automation capabilities.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use a formula to permanently save a date without VBA?

No, standard formulas like TODAY() or NOW() are volatile and will recalculate every time the worksheet calculates or the file is opened. VBA is required to convert a conditional date into a static, hardcoded value.

How do I manually insert a static date without a formula?

You can quickly insert the current static date into any cell by pressing the 'Ctrl' + ';' (semicolon) keys on your keyboard. This value will not update the next day.

Why isn't my Workbook_Open macro running when I open the file?

Your macro settings might be disabling the script. Ensure you have saved the file as a Macro-Enabled Workbook (.xlsm) and clicked 'Enable Content' when the security warning appears.

Can I trigger the static date insertion based on cell edits instead of opening the workbook?

Yes, you can use the Worksheet_Change event in the specific sheet's VBA module to monitor a target cell and insert a static date when that specific cell is modified.

Excel & Data Problems