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...


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


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.