VBA & Macro Problems

Automatically Record an Entry Date in Excel Column C

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

The user wants Excel to record and retain the original entry date in column C when data is entered in column B.

Automatically Record an Entry Date in Excel Column C
Product
Microsoft Excel
Device & OS
not provided
Scenario
Data is entered in column B, starting at row 2, and the adjacent cell in column C should store its entry date.
Observed behavior
Column C should keep the original date when an existing entry in column B is edited and clear the date when that entry is removed.
Before you start

Use desktop Excel for the VBA method, and confirm that column C is available for dates starting at C2. Check that your organization's macro settings allow you to enable trusted macros.

Solution 1Recommended

Use a Worksheet Change Macro to Preserve the Entry Date

Use this method to store a fixed date without enabling iterative calculation.

The macro writes a date only when the adjacent cell in column C is empty. It preserves that date when a value in column B is edited and clears it when the value is removed.

The event runs for direct edits and pastes in column B, not when a formula result changes during recalculation.

1
Format the date column

Select column C, then use Home > Number Format > Short Date or another date format.

2
Open the sheet's code module

Right-click the worksheet tab and select View Code. Use the module for the sheet where you enter the data.

3
Add the event macro

Paste this code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) Dim changedCells As Range Dim cell As Range Set changedCells = Intersect(Target, Me.Range("B2:B" & Me.Rows.Count)) If changedCells Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False For Each cell In changedCells.Cells If Not IsError(cell.Value2) Then If cell.Value2 = "" Then cell.Offset(0, 1).ClearContents ElseIf cell.Offset(0, 1).Value2 = "" Then cell.Offset(0, 1).Value = Date End If End If Next cell CleanUp: Application.EnableEvents = True End Sub

4
Save and test the workbook

Use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Enable macros when prompted, then enter and edit a value in B2 to check that C2 retains its date; clear B2 to check that C2 clears.

Use a Worksheet Change Macro to Preserve the Entry Date
Existing dates and error values: A nonempty cell in column C is treated as an existing date and is not overwritten. The macro skips cells in column B that contain Excel error values.
Free Microsoft Office alternative

Work with Entry-Date Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheets. It does not fix Excel's VBA or calculation settings; check macro compatibility before migrating a workbook that relies on worksheet events.

Enter and format dates in a familiar spreadsheet interface.Open and edit common Microsoft Excel formats, including XLSX.Move everyday spreadsheets to a lightweight Office alternative.Use WPS Office for free for common spreadsheet tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Will the macro change the date when I edit an existing entry?

No. The macro writes a date only if the adjacent cell in column C is empty. Editing a nonempty value in column B leaves an existing date in place.

What happens when I delete an entry in column B?

The macro clears the adjacent date in column C. If you enter a new value in that row later, it records the new entry date.

Can I use the worksheet-change macro in Excel for the web?

No. Add and run the VBA event in desktop Excel. Excel for the web cannot run VBA macros.

Why does the formula need iterative calculation?

The formula in C2 refers to C2 itself to retain its previous value. In desktop Excel, enable iterative calculation under File > Options > Formulas and set Maximum Iterations to 1.