.jpg%3Ftable%3Dblock%26id%3D3c0955d2-04ce-4d36-bd11-62c60acade31%26cache%3Dv2&w=3840&q=75)
I had a spreadsheet collecting a long list of dates, and next to it a calendar I manually entered. I wanted to color cells on the calendar, so that the color indicated the Number of dates in a separate list that fell on that date. I wanted green if it was 1 or 2, Yellow if it was 3 or 4, and Red if it was 5.
It took me too long to figure it out, but here’s how you do it:
Create a “Conditional Formatting” with “Custom Formula”, like so:
=IF(COUNT(FILTER($L$1:$L$200, $L$1:$L$200=DATE(2016,8,A3) ))<5, if(COUNT(FILTER($L$1:$L$200, $L$1:$L$200=DATE(2016,8,A3) ))>2, 1,0),0) |
L1:L200 - The Column of dates.
A3 - The top-left cell containing the calendar (of August 2016 in this case)
This is the comparison for the 3 and 4 case, just change the <5 and >2 to whatever you need.
Hope it helps!
Next step is to get this to work for multiple columns of dates.