What is wrong with this equation. I get Error code 504.

[JoeJP]

W231 =Datevalue('11/1/22)

W218=Datevalue('10/1/21)

 W219=Datevalue('11/1/21)

W220=Datevalue('2/1/21)

Err:504

◄'=IF((W231-W218)>=30=1,66.42,IF(W231-W219)>=60=1,132.84,IF(W231-W220)>=90=1,199.26,0)

[#2]

@JoeJP 

Does this do what you want?

=IF(W231-W218>=90, 1199.26, IF(W231-W219>=60, 1132.84, IF(W231-W220>=30, 166.42, 0)))

or

=IFS(W231-W218>=90, 1199.26, W231-W219>=60, 1132.84, W231-W220>=30, 166.42, TRUE, 0)

Note that the order of the conditions had to be reversed.

[#3]

@JoeJP  wrote:  ``What is wrong with this equation``

To answer your question, the syntax '11/1/22 (leading apostrophe) is valid only as a constant entered by itself into a cell.  As the first character, the apostrophe (single-quote)  tells Excel not to interpret the characters to the right.

Ostensibly, you might enter "11/1/22" (matching double-quotes).  However, I assume you are entering dates in the form DMY.  That might not work if the workbook is opened on a computer that uses MDY dates, for example.

DATE(2022,11,1) is more reliable (portable) than DATEVALUE("11/1/22").

[#4]

@Hans Vogelaar 

Curious:  Is Excel now returning error codes like Error 504 now instead of #VALUE, for exampe ?!

Not according to my google search.  But then again, my google search skills are poor.

OTOH, I believe that other spreadsheet apps (Google Sheets, for example?) do return error codes.

[#5]

@Joe User 

Excel doesn't return error numbers in formulas as far as I know.

Error 504 appears to be a possible error message when Excel crashes, indicating a memory leak...

[#6]

@Hans Vogelaar 

Thank you Hans. Your suggestion works, why I don't know. If each IF statement is entered by itself,

the equations work but not when ganged together.

[#7]

@Hans Vogelaar  wrote:  ``Error 504 appears to be a possible error message when Excel crashes, indicating a memory leak``

Thanks.  I ask because in another context, a user claims that Excel returns "Err:502" where I would expect #NUM among a series of (improper) XIRR formulas.  Clearly, Excel is not "crashing" (terminating unexpectedly) in those instances.

One non-MSFT website claims that Excel returns Error xxx as a "logic error" (distinct from a crash) due to a memory "fault" (computer error) in handling data.  But I would be surprised if Excel does not in fact abort when that happens.

Oh well, it's good to know that Excel has not changed the way that it reports user errors.  Thanks.