Forum Discussion
Solved
Too many parameters with OR function
Hi I am trying to write a quick formula to adjust rate of pay dependent of the date of service.
Here's the formula I've got:
=IF(AND(Table1[@User]="Person's name")*(OR(Table1[@Date]>=DATEVALUE("2021-01-01"),Table1[@Date]<=DATEVALUE("2022-05-31"))),"300"*(OR(Table1[@Date]>=DATEVALUE("2022-06-01"),Table1[@Date]<=DATEVALUE("2023-05-31"))), "310"*(OR(Table1[@Date]>=DATEVALUE("2023-06-01"),Table1[@Date]<=DATEVALUE("2025-05-31"))), "320")
It works fine with just the first two date ranges, but states there are "too many arguments for this function" when I add the third. Is this a limit with the OR function? If so, what else should I use to allow for multiple date range parameters?
Thanks!
Apart from the errors in the formula, it would get unwieldy very quickly if you want to expand it for more date ranges or more users.
I'd create a lookup range like this:
C2, D2 etc. contain the starting date of each date range; B3, B4 etc. the unique user names.
The range can easily be expanded to accommodate more dates and users.
The formula for the rate in your table can then be
=INDEX($C$3:$F$5, MATCH([@User], $B$3:$B$5, 0), MATCH([@Date], $C$2:$F$2))
Apart from the errors in the formula, it would get unwieldy very quickly if you want to expand it for more date ranges or more users.
I'd create a lookup range like this:
C2, D2 etc. contain the starting date of each date range; B3, B4 etc. the unique user names.
The range can easily be expanded to accommodate more dates and users.
The formula for the rate in your table can then be
=INDEX($C$3:$F$5, MATCH([@User], $B$3:$B$5, 0), MATCH([@Date], $C$2:$F$2))
- Thanks for the recommendations.
For reference, could you point out the errors in the formula? I'm rather confused by how it worked with 2 date ranges, but not the third.The formula makes little sense.
1) The condition
OR(Table1[@Date]>=DATEVALUE("2021-01-01"),Table1[@Date]<=DATEVALUE("2022-05-31")
is always true: any date is either after 2021-01-01 or before 2022-05-31 or both. This holds for the other ones too.
2) A return value such as "300" or "310" is a text value, not a number.
3) Multiplying a return value with a condition, as in
"300"*(OR(Table1[@Date]>=DATEVALUE("2022-06-01"),Table1[@Date]<=DATEVALUE("2023-05-31"))
is nonsense.
It might have looked like this:
=IF(Table1[@User]="Person's name", IFS(Table1[@Date]>=DATEVALUE("2023-06-01"), 320, Table1[@Date]>=DATEVALUE("2022-06-01"))), 310, Table1[@Date]>=DATEVALUE("2021-01-01"), 300, TRUE, ""), "")
But that is hard to maintain since both the dates and the rates are hard-coded into the formula.
