Formula Errors

Automatically Add Today's Date to a New Excel Table Row

Nimra MalikNimra Malik Nov 18, 2026 869 views

Question details

Add today's date to a new Excel table row without overwriting existing manually entered dates.

Automatically Add Today's Date to a New Excel Table Row
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.
Before you start

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.

Solution 1Recommended

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.

1
Add a helper column

In the Excel table, add a column named Current Date separate from the column used for manually entered dates.

2
Enter the formula

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.

3
Format the result

Select the Current Date column and choose Home > Number Format > Short Date. Excel can fill the formula down the table as a calculated column.

Use a Separate Column for a Dynamic Date
The date is not permanent: The helper column displays the current date, not the date when the row was created. Use a timestamp method if the date must stay fixed.
Use WPS Spreadsheet

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. 1. Open the spreadsheet: Open WPS Spreadsheet and choose Open to select your Excel workbook.
  2. 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. 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. 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.
Display today's date automatically for populated rowsKeep manual dates separate from formula resultsOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface for everyday work
QA img-9

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.