Conditional format cells in Google Sheets based on [date]+X

Please view the dynamic calendar here: Please copy and paste for testing

I am transforming a check in list of guests booked in a holiday property into a dynamic calendar. (The list/data is greyed out to the right of the calendar in the shared link above.)

The property is empty for some weeks hence the gaps between some check-in dates. Check-out is calculated from =[check-in]+[Nights]. Critical change overs (CCO) are calculated by the formula =IFERROR(IF(AND(VLOOKUP([Check out of departing guest],[Check In_range],1,0),LEN([Check in of departing guest])>0,LEN([Check in of arriving guest])>0),[Check in of departing guest],""),"")

The month and year can be changed (circled green below) to dynamically change guest names per arrival date.

Critical change overs have been highlighted dynamically in the calendar if the month is changed (circled dark red below).

I now need to make the conditional formatting dynamic so the colours in the date cells (01, 02 etc) automatically style cells if the property is occupied (light red) based upon the arrival date and number of nights the guest is staying for. Each date has 3 cells, one left, one centre and one right. There are 3 to help identify events when guests arrive/depart. The best way to illustrate the desired effect is from the graphic below.

Importantly, on 14th December, there is a critical change-over, and the center cell is shaded deep red.

What formula can I use to make the colours change dynamically?

1. Must account for critical change-overs in dark red
2. Where critical change-overs are not relevant, the green colour should be dominant.

I am struggling to think of a way to count dates when guests are staying from the list on the right hand side and style cells accordingly.

Applying conditional formatting on a sheet with multiple merged cells sitting horizontally and vertically across the worksheet is a nightmare to start with.

Having that said, if you are willing to show some craftsmanship, you can get the job done.

Suppose you have the following named ranges:

• Start_Date being all the start dates in your look up table;
• End_Date being all the end dates (which is Start_Date + Nights) in your look up table;
• CCO being all the critical-change-over dates which can be found using the following formula if you have named the columns as shown below in a Table: =IF(MATCH([@[End Date]],[Start Date],0)>0,[@[End Date]],"")

Then follow the steps to set up the conditional formatting. Please note the following are performed in Excel but not google-sheets.

To set up the formatting for the critical-change-over date, you may highlight the following ranges on your worksheet: =$U$5,$U$12,$U$19,$U$26,$U$33,$U$40, then use the following formula as the formatting rule:

=MATCH(V5,CCO,0)>0

To highlight the days on the calendar, you may highlight the following ranges on your worksheet: =$B$5:$D$5,$B$12:$D$12,$B$19:$D$19,$B$26:$D$26,$B$33:$D$33,$B$40:$D$40, then use the following formula as the formatting rule:

=COUNTIFS(Start_Date,"<="&$D5,End_Date,">="&$D5)>0

Then you need to highlight B5:D40, use Format Painter to copy and paste the format to the following ranges one range at a time:E5:G40,H5:J40,K5:M40,N5:P5,Q5:S5

Then you need to manually replace $D5 within the formatting rule for each range with $G5,$J5,$M5,$P5,$S5.

The formatting for the last day (Saturday) is a little bit complicated as there are actually three separate columns: T, U and V and the rules for Column T and Column V are different.

To set up the formatting for Column T, you need to highlight the following ranges: =$T$5,$T$12,$T$19,$T$26,$T$33,$T$40, then use the following formula as the formatting rule:

=COUNTIFS(Start_Date,"<="&$S5,End_Date,">="&$S5)>0

To set up the formatting for Column V, you need to highlight the following ranges: =$V$5,$V$12,$V$19,$V$26,$V$33,$V$40, then use the following formula as the formatting rule:

=COUNTIFS(Start_Date,"<="&$V5,End_Date,">="&$V5)>0

The formatting for Column U has been explained previously in relation to critical-change-over dates.

In the above demonstration, I have also added the conditional formatting for critical change over dates within the week not just for Saturday. If that's not necessary you can ignore. If you also want to have that set up, you can borrow the idea of setting up the formatting for Column U, highlight the relevant cells in Column C, F, I, L, O, and R altogether, and use the same formula but replace V5 with D5.

Let me know if you have any questions. Cheers :)