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

renpilot

New Member
Joined
Dec 4, 2023
Messages
28
Office Version
  1. 2016
Platform
  1. MacOS
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<>""))
 

Attachments

  • Screenshot 2023-12-08 at 00.46.08.png
    Screenshot 2023-12-08 at 00.46.08.png
    34 KB · Views: 19
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top