+ Reply to Thread
Results 1 to 15 of 15

Highlighting days in a calendar based on a pattern.

  1. #1
    Registered User
    Join Date
    04-16-2020
    Location
    New York, USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    11

    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.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Highlighting days in a calendar based on a pattern.

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

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,275

    Re: Highlighting days in a calendar based on a pattern.

    Hi sean and welcome to the forum,

    So you have a 24 different conditional formatting rules behind your worksheet. I don't understand how you want the pattern created? Do you want it based on a list somewhere else on that sheet? Explain how you see the pattern being inputted.
    I have created a yellow CF based on a single day using DateValue(). You might see if that helps in any way.
    Calendar-Perpetual CF DateValue a day.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    04-16-2020
    Location
    New York, USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    11

    Re: Highlighting days in a calendar based on a pattern.

    [DELETED] I didn't quote, causing confusion.
    Last edited by sean.d.reeves; 04-16-2020 at 11:28 AM. Reason: Didn't quote

  5. #5
    Registered User
    Join Date
    04-16-2020
    Location
    New York, USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    11

    Re: Highlighting days in a calendar based on a pattern.

    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?

  6. #6
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Highlighting days in a calendar based on a pattern.

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

  7. #7
    Registered User
    Join Date
    04-16-2020
    Location
    New York, USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    11

    Re: Highlighting days in a calendar based on a pattern.

    Quote Originally Posted by gue2013 View Post
    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.

  8. #8
    Registered User
    Join Date
    04-16-2020
    Location
    New York, USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    11

    Re: Highlighting days in a calendar based on a pattern.

    Quote Originally Posted by gue2013 View Post
    the TON of conditional formatting is overdone
    you need only one per calendar ==> 12 conditional formattings
    Your method would work given that my pattern remains consistent with the calendar format. However, it changes, and may not always play so nicely with the calendar. For example, the pattern end up being 9 characters long, which would mean that the beginning of the second calendar wouldn't coincide with the end of the last. Does that make sense?
    Last edited by sean.d.reeves; 04-16-2020 at 11:32 AM. Reason: 5 still work, 9 doesn't.

  9. #9
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Highlighting days in a calendar based on a pattern.

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

  10. #10
    Registered User
    Join Date
    04-16-2020
    Location
    New York, USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    11

    Re: Highlighting days in a calendar based on a 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.

  11. #11
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,275

    Re: Highlighting days in a calendar based on a pattern.

    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.
    Last edited by MarvinP; 04-16-2020 at 11:58 AM.

  12. #12
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    413

    Re: Highlighting days in a calendar based on a pattern.

    Try attachment.
    Please note that the pattern must be continuous.
    Attached Files Attached Files
    ❖ Please mark your thread is SOLVED if there has been offered a solution that works fine for you.

    ❖ If you like solutions provided by anyone, feel free to add reputation by clicking on ✶ Add Reputation bottom left of their posts.

  13. #13
    Registered User
    Join Date
    04-16-2020
    Location
    New York, USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    11

    Re: Highlighting days in a calendar based on a pattern.

    Quote Originally Posted by MarvinP View Post
    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...

  14. #14
    Registered User
    Join Date
    04-16-2020
    Location
    New York, USA
    MS-Off Ver
    Pro Plus 2013
    Posts
    11

    Re: Highlighting days in a calendar based on a pattern.

    Quote Originally Posted by huuthang_bd View Post
    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.

  15. #15
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Highlighting days in a calendar based on a pattern.

    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).
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Highlighting specific days on a calendar based on work patterns
    By BlissC in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-06-2019, 09:03 AM
  2. Macro to insert pattern based on value and select based on pattern
    By CB569 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-15-2015, 12:36 PM
  3. [SOLVED] Lookup and return rows based on pattern start and pattern end
    By JDI in forum Excel General
    Replies: 18
    Last Post: 11-16-2014, 11:44 PM
  4. Distributing work across the calendar days based on effort taken per task
    By sriramdh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2014, 02:11 AM
  5. Replies: 0
    Last Post: 01-14-2011, 01:05 PM
  6. Changing calendar based on year with static days
    By reggie1000 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2008, 10:56 AM
  7. Calculating based on calendar days
    By Pennypacker in forum Excel General
    Replies: 0
    Last Post: 02-19-2005, 04:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1