Color odd months

Conditional formatting formula: =MOD(MONTH($B6),2)

Color even months

Conditional formatting formula: =NOT(MOD(MONTH($B6),2))

Color odd years

Conditional formatting formula: =MOD(YEAR($B6),2)

Color even years

Conditional formatting formula: =NOT(MOD(YEAR($B6),2))

Border between months

Conditional formatting formula: =MONTH($F7)<>MONTH($F6)

Border between years

Conditional formatting formula: =YEAR($F33)<>YEAR($F32)

Download excel file for this tutorial.

Highlight every even month.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

MOD(number, divisor) returns the remainder after a number is divided by a divisor

NOT(logical)
Changes FALSE to TRUE, or TRUE to FALSE

YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999

MONTH(serial_number) returns the month, a number from 1 (January) to 12 (December)

Related posts:

1. Highlight duplicates on same date, week or month using conditional formatting in excel
2. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
3. How to calculate missing months in a given date range in excel
4. Highlight overlapping date ranges using conditional formatting in excel
5. Count unique distinct months in excel
6. Create unique distinct year and months from a long date listing in excel
7. How to highlight max and min value in every month in excel
8. Create a list of dates with blanks between quarters in excel
9. Visualize date ranges in a calendar in excel
10. Largest value in a range using date criteria in excel