# Not seeing the Err504 in my Calc formula [closed]

I have a column meant to contain the pertinent workweek based on paydate. In this case, the workweek is 3 weeks sooner than the paydate's week.

I have appended the 4 digit year in front of the workweek since the column data will hold more than one year's worth of workweeks (201852, 201901). I tried to create a formula that accounts for a workweek that occurs in late 2018 but is paid 3 weeks later in early 2019.

Column H contains the paydates.

Calc is giving me an Err: 504

=IF(H3="","",IF((SUM(WEEKNUM((H3),21)-3))>9,VALUE(YEAR(H3))&SUM(WEEKNUM((H3),21)-3)),IF((SUM(WEEKNUM((H3),21)-3))>3,VALUE(YEAR(H3)&0&SUM(WEEKNUM((H3),21)-3)),VALUE(SUM(YEAR(H3)-1)&0&IF(H3=3,52,IF(H3=2,51,50)))))


Err 504 Parameter list error: Function parameter is not valid, for example, text instead of a number, or a domain reference instead of cell reference.

I am not finding my mistake causing the error on my own. I'm unsure what I did wrong and need some help fixing my formula. Thanks.

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by erAck close date 2019-05-14 19:30:17.513142

Sort by » oldest newest most voted

The first if gets 4 arguments

=IF(
H3="",
"",
IF(
(SUM(WEEKNUM((H3),21)-3))>9,
VALUE(YEAR(H3))&SUM(WEEKNUM((H3),21)-3)),
IF(
(SUM(WEEKNUM((H3),21)-3))>3,
VALUE(YEAR(H3)&0&SUM(WEEKNUM((H3),21)-3)),
VALUE(SUM(YEAR(H3)-1)&0&IF(H3=3,52,IF(H3=2,51,50)))
)
)


=IF(
H3="",
"",
IF(
(SUM(WEEKNUM((H3),21)-3))>9,
VALUE(YEAR(H3))&SUM(WEEKNUM((H3),21)-3),
IF(
(SUM(WEEKNUM((H3),21)-3))>3,
VALUE(YEAR(H3)&0&SUM(WEEKNUM((H3),21)-3)),
VALUE(SUM(YEAR(H3)-1)&0&IF(H3=3,52,IF(H3=2,51,50)))
)
)
)

more

The 504 error was indeed a bad nesting of an IF() statement as illustrated by @erAck. Thanks.

I also found the way you reformatted it with indentations to make it more legible. I have tried that myself in the past to make them easier to read but never quite came up with anything that looked quite so clean or well structured. It's certainly something I'll try to use to help as I write other formulas.

Now the error is gone, but the formula is still misbehaving. Time to close this issue and open another.

( 2019-05-11 00:14:31 +0100 )edit