Highlighting days in a calendar based on a pattern.

I have created a calendar that auto-populates 12 calendars (one for each month). What I'm trying to do is highlight days on the calendars based on a pattern. The pattern is as follows:

X X O O X X X O O X X O O O (whereas X's are highlighted, and O's are not)

This pattern is perpetual regardless of weekends/holidays. I've been trying to figure out how to do this, without VBA. This spreadsheet is to be used on a work computer, and code is stripped from any documents due to security measures on our network. Optimally, I would like this to be dynamic, so that I can enter the pattern manually because the pattern isn't the same for every system.

To mitigate a lot of questions and confusion, I have attached the calendar in question.

you could use this pattern to generate a table that could be used for conditional formatting.
I did it for one table (see attached file)

Thanks for your reply. Disregard the existing conditional formatting, as it has nothing to do with what I'm trying to accomplish. I'd like to be able to create a pattern in a table or named range then be able to choose a start_date and then have a formula return TRUE/FALSE for another date based on the pattern.

Let's assume the start_date is 01OCT20 (from original attachment), and the pattern as written in my question. I'd like to have a formula that could determine if 29AUG20 would be an X (TRUE) or O (FALSE).

However, I need the formula to still work if the pattern is a different length. Does this clarify?

the TON of conditional formatting is overdone
you need only one per calendar ==> 12 conditional formattings

Originally Posted by gue2013

you could use this pattern to generate a table that could be used for conditional formatting.
I did it for one table (see attached file)

I can see that you understand what I'm trying to do here. Your method could work, as the pattern would flow seamlessly from one calendar to another. However, it does seem like it would take a TON of conditional formatting. I was hoping for a solution that would have a start date (first day of first calendar's month, for example) and then be able to determine if a provided date is TRUE or FALSE (highlighted, or not) based on the pattern entered.

You're on the right path, but I think your solution could be refined. Thank you for your response, I'll continue to think of how this could be implemented.

you only mentioned that the pattern changes not the length.
so you are right and you cannot use this pattern.

I see that I've added far too many details. This has become an issue. I will repost, but change the question, and omit the unnecessary details.

Originally Posted by MarvinP

Hi sean,

You can create Named Range Constants with something like
Pattern = {True,False,False;False,True,False;False,False,False}
The comma is next column and semi colon is next row. Then you can extract True or False using the Index function like The middle true would be =Index(Pattern,2,2)
I think you are close using a Named Range Constant.

I've never seen using a Named Range Constants using True and False for Conditional Formatting but I think it may be possible using an Index formula into the Range. CF complains if you try to use Array formulas in it.

Thanks, again, for your reply. I will have to play with this some more. The issue I'll run in to is that I have to dummy-proof this spreadsheet and make the pattern end-user editable. When the pattern changes, I may not be around to correct it. I'm sure we've all had someone jack up our spreadsheet before...

Originally Posted by huuthang_bd

Try attachment.
Please note that the pattern must be continuous.

This does what I need... THANKS a lot! The CF looks quite confusing to me as-is. I am going to rip this apart, put it back together, and see what sticks. Drives me nuts when I don't know how something I'm using works.

I changed the conditional formatting again (only one!).
Now you need only a table which you can change as you need (content and number of patterns).