Forum Discussion
Too many parameters with OR function
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))
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.
HansVogelaar 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*