# Google sheets with conditional formatting not working

Update: I want one specific cell to become green (filled with green color) if a range of other cells (which contain only dates) count up to 7 or more filled cells AND contain dates within a specific date range.

I have created a logical function which is true but as soon as I want to use it for the conditional formatting the color labeling doesn't work. Is there a specific reason why the following formula works (=becoming true) in a cell but not in the conditional formatting?

``````=AND(A2:A>=DATE(2017;10;12);A2:A<=DATE(2017;10;19);COUNTA(A2:A)>6)
``````

I have a feeling that it is related to the DATE range filter but don't know exactly.

• The formula works for me. Add more details like the address of cells where you applied the conditional formatting. – Rubén Oct 13 '17 at 1:35
• Take a look at this example sheet: docs.google.com/spreadsheets/d/… – Peleke Oct 13 '17 at 8:29

Based on the edit,

Update: I want one specific cell to become green (filled with green color) if a range of other cells (which contain only dates) count up to 7 or more filled cells AND contain dates within a specific date range.

``````=ARRAYFORMULA(AND(\$A\$2:INDEX(\$A\$2:\$A,COUNTA(\$A\$2:\$A))>=DATE(2017,10,12),\$A\$2:INDEX(\$A\$2:\$A,COUNTA(\$A\$2:\$A))<=DATE(2017,10,19),COUNTA(\$A\$2:\$A)>6))
``````

Apply to `A1`

This should work, as long as `ALL` the dates (with no exception) in A column are within the date range specified.

``````=ARRAYFORMULA(AND(SUMPRODUCT(\$A\$2:INDEX(\$A\$2:\$A,COUNTA(\$A\$2:\$A))>=DATE(2017,10,12))>6,SUMPRODUCT(\$A\$2:INDEX(\$A\$2:\$A,COUNTA(\$A\$2:\$A))<=DATE(2017,10,19))>6))
``````

Apply to `A1`

This should work, If any 7 or more of the entries in column A are within the specified date range.

Note: For both the formulas to work, There shouldn't be any blanks in between two entries.

``````=ARRAYFORMULA(AND(SUMPRODUCT(\$A\$2:A>=DATE(2017,10,12))>6,SUMPRODUCT(\$A\$2:A<=DATE(2017,10,19))>6))
``````

This will work in spite of blank entries, if any 7 or more of the entries are within the date range.

Tested and works well

• Thanks a lot, I tried the second version and it works as it was intended by me. Have a great weekend! – Peleke Oct 13 '17 at 14:21