-3

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.

Example sheet: https://docs.google.com/spreadsheets/d/1uH2PZ9YmFNQDmml73iKQNz6sv8tEwbETRo0ywxz6yg4/edit?usp=sharing

  • 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
2

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

  • 1
    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

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.