Automatically Add Today's Date to a New Excel Table Row
Question details
Add today's date to a new Excel table row without overwriting existing manually entered dates.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A table has a date column that should receive today's date when a new row is populated.
- Observed behavior
- A formula in the date column refers to its own cell, causing a circular reference and potentially displaying zero as 1/0/1900.
Identify the table's date column and a column that will be filled when a new row is entered. Decide whether the date should update every day or remain fixed when the row is populated.
Use a Separate Column for a Dynamic Date
Use a helper column when the displayed date should update to the current day rather than remain fixed.
A formula cannot both preserve a manually entered value and calculate a date in the same cell. TODAY() also changes as the workbook recalculates on later days.
In the Excel table, add a column named Current Date separate from the column used for manually entered dates.
In the first data cell of Current Date, enter =IF([@Task]="","",TODAY()), replacing Task with the name of the column used to identify a populated row.
Select the Current Date column and choose Home > Number Format > Short Date. Excel can fill the formula down the table as a calculated column.

Insert a Fixed Date with a Worksheet Change Event
Use a VBA event in desktop Excel to enter a permanent date when a new row's key cell is filled.
Enter a Fixed Date Manually
Use a keyboard shortcut when you want a permanent date without using macros.
Show Today's Date in a WPS Spreadsheet Helper Column
Use a separate formula column in WPS Spreadsheet to display the current date for populated rows while keeping manually entered dates in their own column. The formula date updates; it is not a permanent timestamp.
- 1. Open the spreadsheet: Open WPS Spreadsheet and choose Open to select your Excel workbook.
- 2. Add a helper column: Add a blank column beside your data and label it Current Date. Keep any manually entered dates in a separate column.
- 3. Enter the formula: If the row's identifying value is in B2, enter =IF(B2="","",TODAY()) in the helper cell for row 2. Adjust B2 to match your sheet.
- 4. Fill and format: Use the cell's fill handle to copy the formula down, then select the helper column and choose a date format from the Home tab.

Frequently Asked Questions
Why does my Excel formula display 1/0/1900?
A formula such as =IF(ISBLANK(M2),TODAY()) entered in M2 refers to its own cell, creating a circular reference. A resulting zero can appear as 1/0/1900 when the cell is formatted as a date.
Will TODAY() keep the date when I added the row?
No. TODAY() returns the current date and can change when the workbook recalculates. Use a fixed value or a VBA worksheet-change event for a permanent timestamp.
Can a formula preserve a manually entered date in the same cell?
No. Typing a date into a formula cell replaces the formula. Keep manual dates in a separate column, or use a VBA event that fills only blank date cells.
Why didn't the VBA event fill a date when I added an empty row?
The example event fills the date when a single cell in the Task column receives a value, not when an empty row is created. It also requires macros to be enabled and ignores changes involving multiple cells.




