VBA & Macro Problems

How to Automatically Enter and Keep the Original Date in Excel

Kushani NimanthikaKushani Nimanthika Nov 4, 2026 870 views

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.

How to Automatically Enter and Keep the Original Date in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press the keyboard shortcut Alt + F11 to open the Visual Basic for Applications (VBA) Editor.

2
Locate the Target Worksheet

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.

3
Insert the Event Code

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).

4
Apply the Static Date

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).

5
Save as a Macro-Enabled Workbook

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 a Worksheet_Change VBA Macro
Static Date Preservation: Because the macro pastes the date as a raw value rather than a formula, it will remain perfectly intact when you open the file tomorrow or next year.
Manage Macros Easily with WPS Office

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. 1. Open Your Data File: Launch WPS Spreadsheet and open the worksheet where you track your daily entries.
  2. 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. 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. 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.
Full compatibility with Microsoft Excel .xlsm and .xlsb formatsBuilt-in VBA editor for creating, modifying, and debugging macrosLightweight application that runs complex automated scripts quicklyIntuitive, familiar interface ensuring a zero-learning-curve transition
microsoft office alternative - wps office

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.