SOLVED

Too many parameters with OR function

Copper Contributor

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!

4 Replies
best response confirmed by mathetes (Silver Contributor)
Solution

@BenjaminH2180 

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:

HansVogelaar_0-1710266763134.png

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

 

HansVogelaar_1-1710266933704.png

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.

@BenjaminH2180 

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.

@Hans Vogelaar Thanks. 

 

I'll be honest, I copied and pasted most of that formula from another website. It didn't make much sense to me either, but it worked.... at least for the first two date requests. Or at least it appeared to. *shrug*

1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

@BenjaminH2180 

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:

HansVogelaar_0-1710266763134.png

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

 

HansVogelaar_1-1710266933704.png

View solution in original post