Change cell colors based on number of entries for specific month

What counts as an entry for a particular date? the subsequent 12 columns in the same row?

Assuming this is the case you best option is to add a column after the date like this:

B2=IF(A2="","",IF(COUNTA(C2:N2)<12, "NO", "YES"))

this is shorthand for... select cell B2, then type (or copy and paste from here) the formula:

=IF(A2="","",IF(COUNTA(C2:N2)<12, "NO", "YES"))

now select cell B2 and apply conditional formatting by opening the cell formatter:

The button "Conditional Formatting..." on the right, then add the rules as shown in the in first screenshot

now select cell B2, copy

select all cells in column B except the B1 (click the "B" at the very top, then hold the command key, and single clock B1), paste

Hi GDKennedy,

I am interpreting your question a little differently from Wayne but I am not sure I have met the "simple" requirement.

I needed two hidden columns to do this. The first just finds the month number:

This is filled down the entire table.

The next is where the work gets done. This counts how many dates have the same month number as the date in column A and if that is more than 12 returns the date in column A

F2 =IF(COUNTIF(E,"="&MONTH(A2))>12,A2,"")

This is also filled down the entire column.

Now we can do the conditional highlighting.

This compares the date in column A with the date in column F if they are the same they get highlighted. Notice that there is no $ in the F2 lozenge. I used the little triangle to uncheck "preserve row" and "preserve column". If you don't do this it will only compare your date to F2 down the entire column. Once you set the rule for A2 select the entire column and combine rules.

The default highlight for column A is red. Any value or no value will be red. I say be nice to yourself and only use the green.

quinn

Hello

Also you may try something like the following although it is not exactly what you requested. Column I is auxiliary column. Conditional formatting is applied to column I for simplicity.

Table 1 (excerpt) A1 date A2 2015-07-01 A3 2015-07-02 A4 2015-07-03 I1 12 I2 =COUNTIFS(A,">="&EOMONTH(A2,-1)+1,A,"<"&EOMONTH(A2,0)+1)>=I$1 I3 =COUNTIFS(A,">="&EOMONTH(A3,-1)+1,A,"<"&EOMONTH(A3,0)+1)>=I$1 I4 =COUNTIFS(A,">="&EOMONTH(A4,-1)+1,A,"<"&EOMONTH(A4,0)+1)>=I$1

Notes.

The formula in I2 can be filled down.

You can change the threshold defined in cell I1.

Table is built with Numbers v2.

Regards,

H