Conditional Formatting in an excel dynamic Calendar

[Aggie11]

I have created a dynamic academic calendar in excel. Because the dates change from year to year I have listed the dates that I want conditional formatting in the second sheet. When I do =Match for a single date it works fine.

However when I have a list of dates, for example, 8/1/2021-8/6/2021, 7/1/2021-7-3/2021 etc. and apply the formula for start through end dates: =OR(AND(E25<>"",F25<>"",Calendar!B10<>"",Calendar!B10>=E25,Calendar!B10<=F25),AND(E26<>"",F26<>"",Calendar!B10<>"",Calendar!B10>=E26,Calendar!B10<=F26),AND(E27<>"",F27<>"",Calendar!B10<>"",Calendar!B10>=E27,Calendar!B10<=F27),AND(E28<>"",F28<>"",Calendar!B10<>"",Calendar!B10>=E28,Calendar!B10<=F28),AND(E29<>"",F29<>"",Calendar!B10<>"",Calendar!B10>=E29,Calendar!B10<=F29),AND(E30<>"",F30<>"",Calendar!B10<>"",Calendar!B10>=E30,Calendar!B10<=F30),AND(E31<>"",F31<>"",Calendar!B10<>"",Calendar!B10>=E31,Calendar!B10<=F31),AND(E32<>"",F32<>"",Calendar!B10<>"",Calendar!B10>=E32,Calendar!B10<=F32),AND(E33<>"",F33<>"",Calendar!B10<>"",Calendar!B10>=E33,Calendar!B10<=F33),AND(E34<>"",F34<>"",Calendar!B10<>"",Calendar!B10>=E34,Calendar!B10<=F34),AND(E35<>"",F35<>"",Calendar!B10<>"",Calendar!B10>=E35,Calendar!B10<=F35),AND(E36<>"",F36<>"",Calendar!B10<>"",Calendar!B10>=E36,Calendar!B10<=F36),AND(E37<>"",F37<>"",Calendar!B10<>"",Calendar!B10>=E37,Calendar!B10<=F37))

the conditional formating on my calendar page doesn't work. I can't figure out what I am doing wrong.

 

[Peter Bartholomew]

You say you use MATCH for a single lookup, presumably nested within ISNUMBER. The equivalent condition for a range might be
= CurrentDate <= LOOKUP( CurrentDate, EventStart, EventEnd )
where CurrentDate is a relative reference RC to the current cell and EventStart & EventEnd are columns of the event table.
To allow for blank or zero values in the CurrentDate you could enclose the formula within an IF clause
= IF( CurrentDate, CurrentDate <= LOOKUP( CurrentDate, EventStart, EventEnd ) )

 

[NARAYANK991]

Hi ,

I am not able to understand what and where things don't work.

Can you be specific and explain which are the cells where your existing CF rules do not work ?

Narayan

 

[GraH - Guido]

I understand the OP wants to highlight the dates on his calendar matching with the events mentioned on the sheet "Formula Sheet". There are events with a start date and end date. Hence the long formula with comparison operators.
Just looking at that formula gives me a headache already. It is using relative references all over. How can this correctly work in a CF-rule?
I came up with an array formula yesterday that seemed to work but the wrong cells got highlighted :-(
Looking at it again with a bit more of mental freshness.

I went with these rules:
darkgrey: =SUMPRODUCT((B10>='Formula Sheet'!$E$40:$E$43)*(B10<='Formula Sheet'!$F$40:$F$43))>0
lightgrey: =SUMPRODUCT((B10>='Formula Sheet'!$E$25:$E$37)*(B10<='Formula Sheet'!$F$25:$F$37))>0