Conditional formatting by month based on year

Question

Working with Google Sheets, I'm currently making a year schedule. The sheet should be recycled every year.

• Column A has several dates.
• D2 is the year, e.g. 2015, 2016 or 2017.

I'm trying to apply conditional formatting to column A. Every month to get its own colour. January coldblue, February blue, March little bit green, et cetera.

The easy way is to use date before .., like 'date before 1-2-2015' for January. But then, the conditional formatting should be altered every year.

How can I use custom formulae (in conditional formatting) using the year from D2 + before start of the next month?

Answer 1

I would not recommend that many different colours (if only because each colour after the first needs a separate rule!) and don't really see the point of D2 for the CF - unless you plan to have a separate set of 12 colours for each year? However, I think what you request is a plethora of formulae like:

=and(year(A1)=D$2,month(A1)=1) 

to be repeated with the last 1 changed in integer steps all the way to 12, with a different colour for each such value.