Fix an Excel Formula Error When Autofilling Today's Date
Question details
The user needs a formula that displays the current date when another cell contains data.

- 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().
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.
Use an IF and TODAY Formula in a Separate Cell
Use this formula to show today's date whenever A1 contains data.
Select B1 and enter =IF(A1<>"",TODAY(),""). Press Enter.
If Excel still reports a formula error, try =IF(A1<>"";TODAY();"") in B1. Some regional settings use semicolons instead of commas.
Select B1, then use Home > Number Format > Short Date if the result appears as a number.
Drag the fill handle at the bottom-right corner of B1 down the column to apply the formula to other rows.

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. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
- 2. Enter the date formula: Select B1 and enter =IF(A1<>"",TODAY(),""). If your regional settings require semicolons, use =IF(A1<>"";TODAY();"") instead.
- 3. Format the date: Select B1 and choose a date format from the number format controls on the Home tab.
- 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.

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.




