CODEWITHSUNDEEP
excelconditional-formatting
Peter.Gasparik
Peter.Gasparik

Reputation: 13

Excel conditional formatting - Highlight the cell in Calendar if the start date and end date is the same

I have a question, is it possible to extend this formula: I have conditional formatting which highlight the cells in calendar based on the start date and end date where is there is text „Studio“.

Start date:
=ISNUMBER(MATCH(1,(accomodation_2024!$F$7:$F$101="Studio")*(accomodation_2024!$G$7:$G$101=D5),0))

End date:
=ISNUMBER(MATCH(1,(accomodation_2024!$F$7:$F$101="Studio")*(accomodation_2024!$H$7:$H$101=D5),0))

This formula basically firstly check whether in sheet accomodation_2024 is in column F text „Studio“ and than if yes than will highlight the start date in calendar based on column G and highlight the end date based on column H.

Is it possible to adjust this formula to find the same date (or overlapping date) in two columns F and H? It firstly need to also check for the „Studio“ condition. For example, I have start date in column G (08.01.2024) and end date in column H (08.01.2024).

Sometime the start date is the same as end date. The same precondition should applied if "accomodation_2024!$F$7:$F$101="Studio" than highlight cell where the start date is the same as end date. Thanks.

Edit: 31.01.2024 - added images of table, calendars and rules

Table accomodation_2024:
table "accomodation_2024"

calendars:
calendars

rules:
Rules

Upvotes: 1

Views: 161

Answers (1)

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2619

Try this:

=COUNTIFS(accomodation_2024!$F$2:$F$97,"Studio",accomodation_2024!$G$2:$G$97,D5)*COUNTIFS(accomodation_2024!$F$2:$F$97,"Studio",accomodation_2024!$H$2:$H$97,D5)

You can also use similar formulas for the other conditional formattings:

=COUNTIFS(accomodation_2024!$F$2:$F$97,"Studio",accomodation_2024!$H$2:$H$97,D5)
=COUNTIFS(accomodation_2024!$F$2:$F$97,"Studio",accomodation_2024!$G$2:$G$97,D5)
=COUNTIFS(accomodation_2024!$F$2:$F$97,"Studio",accomodation_2024!$G$2:$G$97,"<"&D5,accomodation_2024!$H$2:$H$97,">"&D5)
=COUNTIFS(accomodation_2024!$F$2:$F$97,"Studio",accomodation_2024!$G$2:$G$97,"<="&D5,accomodation_2024!$H$2:$H$97,">="&D5)=0

The last one especially since your red-coloring conditional formatting doesn't work as it should (it won't paint red the second week of jan 2024, for example).

Upvotes: 1

Related Questions