Formula Errors

Fix an Excel Formula Error When Autofilling Today's Date

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 870 views

Question details

The user needs a formula that displays the current date when another cell contains data.

Fix an Excel Formula Error When Autofilling Today's Date
Product
Microsoft Excel
Device & OS
not provided
Scenario
Enter a date automatically in a cell when a related cell is filled.
Observed behavior
Excel displays “There's a problem with this formula” when the user enters a nested IF formula using NOW().
Before you start

Identify the cell to check for data and a separate cell for the date. The examples below check A1 and display the result in B1.

Solution 1Recommended

Use an IF and TODAY Formula in a Separate Cell

Use this formula to show today's date whenever A1 contains data.

1
Enter the formula

Select B1 and enter =IF(A1<>"",TODAY(),""). Press Enter.

2
Check the separator

If Excel still reports a formula error, try =IF(A1<>"";TODAY();"") in B1. Some regional settings use semicolons instead of commas.

3
Format the result as a date

Select B1, then use Home > Number Format > Short Date if the result appears as a number.

4
Fill down

Drag the fill handle at the bottom-right corner of B1 down the column to apply the formula to other rows.

Use an IF and TODAY Formula in a Separate Cell
The date is not a fixed timestamp: TODAY() updates when the workbook recalculates, and the result becomes blank if A1 is cleared. A formula in B1 that refers to B1 itself creates a circular reference; use a manual entry or VBA if you need a fixed entry date.
Work with dates in WPS Spreadsheets

Autofill Today's Date in WPS Spreadsheets

Use IF and TODAY in WPS Spreadsheets to display the current date when a related cell contains data.

  1. 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
  2. 2. Enter the date formula: Select B1 and enter =IF(A1<>"",TODAY(),""). If your regional settings require semicolons, use =IF(A1<>"";TODAY();"") instead.
  3. 3. Format the date: Select B1 and choose a date format from the number format controls on the Home tab.
  4. 4. Apply it to other rows: Drag B1's fill handle down to copy the formula, then enter data in the corresponding cells in column A.
Show today's date based on another cell's contentOpen and save Microsoft Excel XLSX filesFill a formula down a column for multiple rowsUse familiar spreadsheet formatting controls
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel reject the commas in my formula?

Your regional settings may require semicolons as argument separators. In B1, try =IF(A1<>"";TODAY();"") instead.

What's the difference between TODAY() and NOW()?

TODAY() returns the current date, while NOW() returns the current date and time. Use TODAY() in the IF formula when only the date is needed.

Why does the date change after I enter it?

TODAY() is recalculated, so its result can change to the current date. For a fixed date, enter it manually or use a VBA solution.

Why does Excel report a circular reference?

A circular reference occurs if the formula refers to its own cell. For example, put =IF(A1<>"",TODAY(),"") in B1, not A1.