Fix Excel Error 504 in a Date-Based IF Formula
Question details
The user needs to correct a nested IF formula that compares dates and returns different payment amounts.

- 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.
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.
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.
Click the cell where the payment amount should appear, then click the formula bar.
Enter =IF(W231-W218>=90,199.26,IF(W231-W219>=60,132.84,IF(W231-W220>=30,166.42,0))) and press Enter.
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.

Use DATE for fixed reference dates
Use DATE when the reference dates should be written directly into the formula rather than stored in cells.
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. Open the workbook: In WPS Spreadsheets, use Open to select the Excel workbook.
- 2. Check the date cells: Inspect W231 and W218:W220 and make sure they contain the intended date values.
- 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. Verify and save: Press Enter, check the payment against the date conditions, and use File > Save or Save As to save the workbook.

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.




