Forum Discussion
What is wrong with this equation. I get Error code 504.
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) |
6 Replies
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").
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.
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.
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.
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...
