How to Keep an Excel Date from Changing When the Workbook Opens
Question details
The user needs a method to record a completion date in a spreadsheet that remains static and does not update when the file is reopened.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- When tracking completion dates, using standard date formulas like TODAY() causes the date to recalculate every time the workbook is accessed.
- Observed behavior
- The TODAY() function updates automatically upon opening the workbook, which overwrites the original recorded completion date with the current date.
Ensure you are prepared to save your workbook in a Macro-Enabled format (.xlsm), as standard workbooks (.xlsx) cannot store VBA code.
Use a Worksheet_Change Macro to Insert Static Dates
Use a VBA event macro to automatically convert your entry into a permanent, static date that will not recalculate upon reopening the workbook.
Because the TODAY() and NOW() functions are volatile and recalculate based on the system clock, they cannot be used to lock in an original completion date. Instead, a Worksheet_Change macro can detect when a cell is updated and automatically insert the current system date as a fixed value into an adjacent cell.
Open your workbook and press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer panel on the left side, double-click the specific worksheet (e.g., 'Sheet1') where you want the completion date to be recorded. Do not insert this into a standard Module.
Copy and paste the following code into the blank code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim targetRange As Range Set targetRange = Range("J4:K100") If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Target, targetRange) Is Nothing Then If Target.Column = 10 And Target.Value <> "" Then Target.Offset(0, 2).Value = Date ElseIf Target.Column = 11 And Target.Value <> "" Then Target.Offset(0, 1).Value = Date End If End If End Sub
Close the VBA editor. Go to 'File' > 'Save As', and change the file type to 'Excel Macro-Enabled Workbook (*.xlsm)'. Now, whenever data is entered in the range J4:K100, the macro will automatically stamp a static date in the offset columns.
Run VBA Macros Seamlessly in WPS Spreadsheet
WPS Spreadsheet offers powerful built-in support for VBA macros, allowing you to easily automate fixed date entries and complex tasks without worrying about formula recalculations.
- 1. Open your Workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the workbook where you want to add the timestamp macro.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click the 'VB Editor' button to access the coding environment.
- 3. Insert the Event Code: Double-click your active worksheet in the Project window and paste the Worksheet_Change macro code to automate static date entry.
- 4. Save the Document: Click 'File' > 'Save As' and choose the '.xlsm' (Macro-Enabled) file format to ensure your new VBA script functions every time the file is opened.
Frequently Asked Questions
Why does the TODAY function change every time I open my workbook?
The TODAY() and NOW() formulas are known as 'volatile functions'. They recalculate automatically based on your computer's system clock whenever the workbook is opened or any background calculation is triggered, which makes them unsuitable for static timestamps.
Can I insert a static date without using VBA macros?
Yes. If you only need to enter the current date occasionally, you can manually insert a static date by selecting the target cell and pressing 'Ctrl + ;' (semicolon) on your keyboard. This value is hardcoded and will never change automatically.
Where exactly should I place the VBA code for a Worksheet_Change event?
Worksheet_Change code must be placed directly in the specific worksheet's code module (e.g., 'Sheet1') where you want the action to occur. It will not execute if placed in a standard module (like 'Module1') or within the 'ThisWorkbook' module.
