Conditional Formatting Days in a Dynamic Calendar

[Conabear]

Hi

I have a question that I think is related to conditional formatting.

I have created a dynamic calendar. The dates rearrange as you change the year. The week counter has been corrected. The next item I wanted to do is have certain days change color depending on their position in the month. To start with I would like to have every 2nd Wednesday highlighted in orange. Start in 2021, date would be Jan 6th then Jan 20, then Feb 3, ....., Dec 22. It's not that date that is important but every second Wednesday. Then when the year changes this formatting changes to reflect so in 2022 it would be Jan 5th then Jan 19th etc.
In the sample calendar I have just filled the fill with orange

[AliGW]

As the position in the calendar will be the same regardless of the year, I see no point at all in using CF for this. CF should only be used where nothing else will work as it can begin to slow down your workbook if overused.

[Conabear]

The position changes from year to year on the calendar because months change from having 4 weeks to 5 week or 5 weeks to 6 weeks or visa versa

Example: May starts on Saturday, week 17 and ends on Monday, week 22, a total of 6 weeks. In 2022 May starts on Sunday, week 18 and ends on Wednesday, week 22, only 5 weeks and that changes the position of the cell.

[AliGW]

OK - I see. Let me have a think.

[josephteh]

Try CF formula below, applied to cells C7:I12 and L7:R12:

Please Login or Register  to view this content.

[josephteh]

Change to:

Please Login or Register  to view this content.

[Conabear]

I copied the formula over and the attached file shows what I get. It does not appear to work unless I am doing something wrong.

[josephteh]

Sorry.. misread your question.. it's every second Wednesday:

For January table:=C7=AGGREGATE(15,6,C$7:C$12/(MONTH(C$7:C$12)=MONTH(C$8)*(TEXT(C$7:C$12,"ddd")="Wed")),2)
For February table:=L7=AGGREGATE(15,6,L$7:L$12/(MONTH(L$7:L$12)=MONTH(L$8)*(TEXT(L$7:L$12,"ddd")="Wed")),2)

[Conabear]

Thanks josephteh

I did notice that and tried the change. It does high light the second Wednesday, Jan 13, but only that one. I need it to start on and highlight Jan 6 and then every other Wednesday after, then change as the year change.
I also correct Feb formula to reference L7 to L12 and changed the 3 to a 2, it highlights Feb 3rd and Feb 10th, the 3rd is correct but not the 10th.

[JohnTopley]

In D4

=DATE($E$2,MONTH(C$4&0),1)+7-WEEKDAY(DATE($E$2,MONTH(C$4&0),1)+3)

This is date of first Wednesday of the month

in CF

=OR(C7=$D$4,C7=$D$4+14)

Repeat for all months.

You can hide the first formula elsewhere (or set Font colour to white)

[Conabear]

Thank you JohnTopley

It started out right but there are still a few glitches. The first starts in March of 2021. If we are highlighting every second Wednesdays should not March 31st be highlighted? If you move through the years, such as 2023, it has the first 3 Wednesdays highlighted.

[josephteh]

Thanks josephteh

I did notice that and tried the change. It does high light the second Wednesday, Jan 13, but only that one. I need it to start on and highlight Jan 6 and then every other Wednesday after, then change as the year change.
I also correct Feb formula to reference L7 to L12 and changed the 3 to a 2, it highlights Feb 3rd and Feb 10th, the 3rd is correct but not the 10th.

How can Jan 6 be second Wednesday of Jan?

[JohnTopley]

Yes . March 31st should be highlighted,

Try

=AND(MONTH(C7)=MONTH($D$4),OR(C7=$D$4,C7=$D$4+14,,C7=$D$4+28))

This assumes 1st/3rd/5th Wednesday WITHIN a month. If you need to treat the year as a contiguous set of Wednesdays then the above will not apply.

(May be possible using WEEKNUM ????)

The layout of the calendar does not lend itself the an easy CF formula for considering the year as a whole.

[AliGW]

How can Jan 6 be second Wednesday of Jan?

I agree - it can’t be!

[JohnTopley]

If you want contiguous 2 weeks:

=AND(MONTH(C7)=MONTH($D$4),MOD((C7-$D$4),14)=0)

Adjust BOLD for each month

I cannot test for others years as I don't have 365 and changing YEAR results in #NAME errors ( formulae not in Excel 2010))

[Conabear]

Dec 23 of 2020 was the previous selected Wednesday then two weeks later we hit Jan 6th 2021, this is why I need it to run year to year as well. The same will happen next year, last Wednesday that would be selected in 2021 will be Dec 22 then, two weeks later, Jan Jan 5 of 2023, which happens to be the first Wednesday of Jan. in 2023.

[AliGW]

I think the issue we have is this idea of the 'second Wednesday' of each month. It's not really that at all, is it?

[JohnTopley]

See post #15

[Conabear]

Ok I copy this new formula into my test calendar, it does highlight every second Wednesday for each month but not every second Wednesday consecutively through the year , maybe I've asked the question wrong.

I need every second Wednesday in the calendar to be highlighted, starting with Jan 6, Jan 20, Feb 3, Feb 17, Mar 3, Mar 17, Mar 31, etc....

Also if this is not possible with CF, is there any other solutions?

P.S.
I also attached an entire calendar year.

[JohnTopley]

Formulae for February

=AND(MONTH(L7)=MONTH($M$4),OR(L7=$M$4,L7=$M$4+14,,L7=$M$4+28))

Mea Culpa: I did not highlight Month change in my previous post: similar for all months.

However, if you had checked the posted file (which was correct!) you would have spotted this.

[JohnTopley]

Alternative the CF is VBA

[Conabear]

Thanks JohnTopley

Just after I posted a found the mistake, I'm in the process of correcting. Looking good so far. Thank you.

[JohnTopley]

See attached - Sheet1.

If you base the MOD of first Wednesday of 2021 ( 6th Jan) then all following years should follow.

=AND(MONTH(C7)=MONTH($D$4),MOD((C7-$D$4),14)=0)

$D$4 is 6th jan 2021 so FIX this in another cell and change the bold to this new cell reference e.g B2 in your calendar

[Conabear]

The last suggestion that JohnTopley gave fixed the issue and the calendar works perfect so thank you everyone for all your help.

[JohnTopley]

You're welcome.