I can not use conditional formatting in Excel 2016 for Mac because the formula is too long

[renpilot]

Hello all,

I am running Excel 2016 for Mac.

I found an old thread in the forum stating that conditional formatting can only contain 256 characters. It was from2010 so I would like to verify this is still the case?

If "yes" I have a major problem in a calendar system I have created to show bookings by A,B C,D or E. I want any cell in a year to change colour if one of 45 date ranges are true. See screenshot.

Example: A has a booking from 1/1/24 to 7/1/24 so the colour of these dates in the calendar should change colour. The same with B,C,D or E. The dates are entered as start date and end date in separate named ranges. At the bottom is an example of a formula that returns a value of true for the named ranges OURA1:OURB10 in a normal cell but can not fit in the conditional formatting formula field because it allegedly is too long.
B, C and D also has 10 possible date inputs. E has 5 possible date inputs, so all in all a lot of possible date ranges with Start Date and End Date to be checked.
The conditional formatting works with a smaller number of date entries, but when the formula gets above the 256 character length I can't write any more arguments in the conditional formatting formula field (in other words it is full).

Any help regarding how to solve this is appreciated

=OR(AND(B2>=OURA1;B2<=OURB1;B2<>"";OURA1<>"";OURB1<>"");AND(B2>=OURA2;B2<=OURB2;B2<>"";OURA2<>"";OURB2<>"");AND(B2>=OURA3;B2<=OURB3;B2<>"";OURA3<>"";OURB3<>"");AND(B2>=OURA4;B2<=OURB4;B2<>"";OURA4<>"";OURB4<>"");AND(B2>=OURA5;B2<=OURB5;B2<>"";OURA5<>"";OURB5<>"");AND(B2>=OURA6;B2<=OURB6;B2<>"";OURA6<>"";OURB6<>"");AND(B2>=OURA7;B2<=OURB7;B2<>"";OURA7<>"";OURB7<>"");AND(B2>=OURA8;B2<=OURB8;B2<>"";OURA8<>"";OURB8<>"");AND(B2>=OURA9;B2<=OURB9;B2<>"";OURA9<>"";OURB9<>"");AND(B2>=OURA10;B2<=OURB10;B2<>"";OURA10<>"";OURB10<>""))

 

[StephenCrump]

You're welcome. I'm glad you figured it out.