How to Automatically Enter and Keep the Original Date in Excel
Question details
The user needs a way to automatically insert a date when a record is entered and keep that original date permanently, as opposed to using the TODAY() function which updates daily.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Entering new records into a spreadsheet and tracking the exact historical creation date for each row without it recalculating.
- Observed behavior
- Using the standard TODAY() or NOW() functions causes the dates on all past records to recalculate to the current date whenever the workbook is opened or refreshed.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your security settings to allow VBA scripts to run.
Use a Worksheet_Change VBA Macro
Add a VBA script to the worksheet module that detects when you enter data and automatically inserts a static date value into the specified column.
Unlike the TODAY() function which is volatile and recalculates constantly, a VBA Worksheet_Change event triggers exactly once when data is modified. It inserts the current date as a hardcoded text or value, permanently preserving the original entry date.
Press the keyboard shortcut Alt + F11 to open the Visual Basic for Applications (VBA) Editor.
In the Project Explorer pane on the left, find your workbook and double-click the specific worksheet where you want the automatic dates to appear.
In the code window, use the left dropdown to select 'Worksheet' and the right dropdown to select 'Change'. Write an IF statement checking if the modified cell (Target) falls within your data entry columns (e.g., Columns B through Z).
Inside the IF statement, instruct VBA to place the current date in Column A of the Target's row (e.g., Cells(Target.Row, 1).Value = Date).
Close the VBA Editor. Go to File > Save As and select 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown so your macro functions correctly.

Use Keyboard Shortcuts for Manual Static Dates
If you prefer not to use VBA macros, you can quickly insert a static date manually using a built-in keyboard shortcut.
Automate Static Dates Seamlessly in WPS Spreadsheet
WPS Spreadsheet offers powerful, built-in support for VBA macros, allowing you to easily configure Worksheet_Change events to track data entry dates without any friction.
- 1. Open Your Data File: Launch WPS Spreadsheet and open the worksheet where you track your daily entries.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab on the top ribbon and click on 'VBA Editor' to open the script environment.
- 3. Add the Worksheet_Change Code: Double-click your target sheet in the Project window and paste your VBA code to monitor data entry columns and output the date.
- 4. Save the Document Properly: Click 'Save As' and choose the Macro-Enabled Workbook format to ensure your automation triggers reliably every time you add a record.

Frequently Asked Questions
Why does the TODAY() function change my dates?
The TODAY() and NOW() functions are known as 'volatile' functions. This means they recalculate every single time the worksheet is opened or whenever any calculation is performed, designed strictly to show the present system date rather than historical entry dates.
Can I use Circular References to keep the original date without VBA?
Yes, by enabling 'Iterative Calculation' in Excel Options, you can create a circular reference formula (e.g., =IF(B2<>"", IF(A2="", TODAY(), A2), "")) that locks the date upon initial entry. However, VBA is generally recommended as it is much more stable and less prone to accidental calculation errors.
Why isn't my Worksheet_Change macro triggering?
Ensure that you have placed your code in the specific Worksheet object module (e.g., Sheet1) and not a standard Module. Additionally, check that your macro security settings allow macros to run, and that Application.EnableEvents is currently set to True.
How do I ensure my VBA macro isn't lost when I close the file?
You must save the file in a macro-enabled format, such as .xlsm (Macro-Enabled Workbook) or .xlsb (Binary Workbook). If you save your work as a standard .xlsx file, Excel will permanently strip the VBA code during the save process.




