Google-sheets – Problem with conditional format of a calendar like table

conditional formattinggoogle sheets

I created a minimal sheet to demonstrate the problem: https://docs.google.com/spreadsheets/d/1cc8UdkOvIPf_wDGCHaDN0BqR3sX3Teb1qKlvdM1cL3g/edit?usp=sharing

Cell A1 specifies a startDate

Cell A2 displays an endDate.

Cells B4:H8 lists the days of 5 consecutive weeks starting with monday.

Cells A4:H4 compute mondays's date from the startDate.

Cells B4:H8 display the result of the following formula:

=AND($A4 + COLUMN(B4) - 2 >= startDate, $A4  + COLUMN(B4) - 2  < endDate)

As you can see in B4:H8, this formula provides the correct result: true for all dates between startDate inclusive and endDate exclusive.

I can't figure out why this formula does not work to apply conditional formatting…

Best Answer

Exactly why your formula does not work I do not know, but I think this version does:

=AND($A4+COLUMN()-2>=$A$1,$A4+COLUMN()-2<$A$2)

This might be counted as an explanation of sorts:

You cannot use Named Ranges directly in conditional formatting, you need to reference them indirectly.

I suspect it is to do with security/permissions.

Related Topic