Formula Errors

Fix Excel Error 504 in a Date-Based IF Formula

Olivia MillerOlivia Miller Nov 18, 2026 869 views

Question details

The user needs to correct a nested IF formula that compares dates and returns different payment amounts.

Fix Excel Error 504 in a Date-Based IF Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
A spreadsheet calculates a payment based on the number of days between a date and one of several reference dates.
Observed behavior
The improperly structured formula produces error 504 instead of the expected payment amount.
Before you start

Check that W231 contains the date to evaluate and that W218, W219, and W220 contain valid reference dates. Confirm the intended payment for each condition and which condition should take priority if more than one is true.

Solution 1Recommended

Correct the nested IF formula using date cells

Use this formula when the reference dates are stored in W218, W219, and W220.

Each IF condition must be a valid comparison. The formula checks conditions in order and returns the amount for the first true condition.

Because each condition uses a different reference date, checking the 90-day condition first sets its priority; it does not guarantee that it is always the most restrictive condition.

1
Select the result cell

Click the cell where the payment amount should appear, then click the formula bar.

2
Enter the corrected formula

Enter =IF(W231-W218>=90,199.26,IF(W231-W219>=60,132.84,IF(W231-W220>=30,166.42,0))) and press Enter.

3
Check the result

Compare the result with the dates in W231 and W218:W220. If multiple conditions are true, confirm that the first matching condition returns the intended amount.

Correct the nested IF formula using date cells
About error 504: Error 504 is not a standard Excel error code; it may come from another spreadsheet application. In Excel, an invalid formula may instead show a formula warning or an error such as #VALUE!.
Calculate date-based payments

Correct the date formula in WPS Spreadsheets

Open the workbook in WPS Spreadsheets and enter a nested IF formula to compare dates and return the appropriate payment.

  1. 1. Open the workbook: In WPS Spreadsheets, use Open to select the Excel workbook.
  2. 2. Check the date cells: Inspect W231 and W218:W220 and make sure they contain the intended date values.
  3. 3. Enter the formula: Select the payment result cell and enter =IF(W231-W218>=90,199.26,IF(W231-W219>=60,132.84,IF(W231-W220>=30,166.42,0))) in the formula bar.
  4. 4. Verify and save: Press Enter, check the payment against the date conditions, and use File > Save or Save As to save the workbook.
Calculate payments using date comparisons and nested IF conditionsOpen and save Microsoft Excel XLSX filesEdit formulas in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return the wrong payment when two conditions are true?

Nested IF checks conditions from left to right and returns the first true result. Edit the formula in the formula bar to put the condition that should take priority first.

Can I use IFS instead of nested IF?

Yes, if your spreadsheet version supports IFS. Enter =IFS(W231-W218>=90,199.26,W231-W219>=60,132.84,W231-W220>=30,166.42,TRUE,0) in the result cell. The final TRUE condition returns 0 when none of the earlier conditions match.

Why should I use DATE instead of typing a date as text?

Text such as 11/1/22 can be interpreted differently under different regional settings. In the formula bar, use DATE(2022,11,1) for November 1, 2022.

What should I check if Excel displays #VALUE!?

Check W231 and the reference cells to make sure they contain real date values rather than text. Re-enter a date using your local date format if necessary, then recalculate the formula.

What if the formula is rejected when I enter it?

Check that every IF has a condition, a result for TRUE, and a result for FALSE, with matching parentheses. If your regional settings use semicolons as argument separators, replace the formula's argument commas with semicolons.