Ask Your Question
0

Not seeing the Err504 in my Calc formula [closed]

asked 2019-05-10 16:00:48 +0100

err504 gravatar image

updated 2019-05-11 00:07:32 +0100

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 flag offensive 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

1 Answer

Sort by » oldest newest most voted
0

answered 2019-05-10 16:34:46 +0100

erAck gravatar image

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)))
       )
    )

Probably that instead should be

=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)))
           )
       )
   )
edit flag offensive delete link more

Comments

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.

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

Question Tools

1 follower

Stats

Asked: 2019-05-10 16:00:48 +0100

Seen: 69 times

Last updated: May 11