VBA & Macro Problems

How to Automatically Enter Today's Date When Data Is Added in Excel

Steve K Oct 16, 2026 870 views

Question details

The user needs a way to automatically populate the current date into a specific column whenever new data is entered in a corresponding row of another column, and to automatically clear the date if the source data is deleted.

How to Automatically Enter Today's Date When Excel Data Is Added

Product: Excel
Device & OS: not provided
Scenario: Entering rows of data into a spreadsheet and requiring an automatic, static timestamp in an adjacent column without typing it manually.
Observed behavior: The date column automatically updates with the current date upon initial data entry and clears its contents automatically when the source data is removed.

Before you start

Ensure you have identified which column will receive your data entries (e.g., Column A) and which column should display the automated date timestamp (e.g., Column B). You must also be comfortable saving your file as a Macro-Enabled Workbook to retain the functionality.

Solution 1Recommended

Use a Worksheet_Change VBA Macro

Applying a Worksheet_Change event macro will automatically monitor your input column, insert the current date when new data is entered, and clear the date if the data is removed.

This automated method relies on VBA code that runs in the background. It listens for any changes in a specified target range (like Column A). When it detects a new entry, it immediately places today's date in the adjacent cell (like Column B). Crucially, it replaces a standard formula by creating a static timestamp that won't update the next day.

Open the VBA Editor

Right-click the specific worksheet tab at the bottom of your Excel window and select 'View Code'. This action will open the Visual Basic for Applications (VBA) editor.

Paste the Event Code

In the blank code module, paste the following script: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Columns("A")) Is Nothing Then Exit Sub Application.EnableEvents = False Dim cell As Range For Each cell In Intersect(Target, Me.Columns("A")) If cell.Value = "" Then Me.Cells(cell.Row, "B").ClearContents ElseIf Me.Cells(cell.Row, "B").Value = "" Then Me.Cells(cell.Row, "B").Value = Date End If Next cell Application.EnableEvents = True End Sub

Adjust Column References

If your input data is not in Column A, modify 'Me.Columns("A")' to your designated column. Likewise, change '"B"' to the column letter where you want the dates to appear.

Save as a Macro-Enabled Workbook

Close the VBA editor window. Navigate to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown. If you save it as a standard .xlsx file, the code will be lost.

Enable Macros: The first time you reopen this workbook, Excel may display a yellow security warning bar at the top. Click 'Enable Content' to allow the macro to run.

Solution 2

Use an IF Formula with Iterative Calculation

If you cannot use or prefer not to use VBA macros, you can enable iterative calculations to create a self-referencing formula that generates a static date.

Automate with WPS Spreadsheet

Automate Your Spreadsheets with WPS Office

WPS Spreadsheet fully supports advanced VBA macros, enabling you to automate repetitive tasks like date entry effortlessly. It provides a familiar, fast, and feature-rich environment for processing all your daily data entry needs.

1. Install WPS Office: Download and install the free WPS Office suite on your device.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing data entry workbook.
3. Access the Developer Tools: Navigate to the Developer tab, click on VBA Editor, and paste your automated date code seamlessly.

Fully compatible with Microsoft Excel formats (.xlsx and .xlsm).Robust VBA macro support for advanced data automation.Lightweight software that runs smoothly without slowing down your PC.Free and intuitive tabbed interface for easier spreadsheet management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my date update to the current day when I reopen the file?

If you are using the standard =TODAY() or =NOW() formula without VBA or iterative calculation, Excel automatically recalculates the formula to reflect the current system date every time the workbook is opened or refreshed. Using the Worksheet_Change VBA macro ensures the date is inserted as static text that will not change.

How do I clear the date automatically when the data is removed?

The Worksheet_Change VBA code provided includes an 'If cell.Value = ""' condition. This means the macro continually watches the data entry cell; if it detects that the data has been deleted or cleared, it uses the ClearContents method to automatically remove the date in the corresponding timestamp cell.

Why isn't my VBA macro working when I enter data into the column?

First, ensure that macros are enabled in your Trust Center settings and that the file is saved as an .xlsm (Macro-Enabled Workbook). Second, double-check that you placed the code in the specific Worksheet module (e.g., Sheet1) rather than a regular module. Lastly, ensure the target range in the code (e.g., Columns("A")) exactly matches the column where you are typing your data.

Excel & Data Problems