Author: Oscar Cronquist Article last updated on December 10, 2018

Color odd months

Conditional formatting formula:


Explaining CF formula in cell B6

Step 1 - Calculate number of month

The MONTH function returns a number representing a month based on a date. 1 = January, 2 = February ... 12 = December.






and returns 1. 1 = January.

Step 2 - Calculate remainder

The MOD function returns the remainder after a number is divided by a divisor.



MOD(1, 2)

and returns 1.

In cell B10 the date changes to 2/2/2008. MOD(MONTH($B6),2) becomes MOD(2,2) and returns 0 (zero). This cell is not highlighted with this specific color.

How to apply conditional formatting

  1. Select cell range B6:D19
  2. Go to tab "Home" on the ribbon
  3. Press with mouse on "Conditional Formatting" button
  4. Press with mouse on "New Rule.."
  5. Select "Use a formula to determine which cells to format"
  6. Type =MOD(MONTH($B6),2) in field "Format values where this formula is true:"
  7. Then press with left mouse button on "Format..." button
  8. Go to tab "Fill"
  9. Pick a color
  10. Press with left mouse button on OK button
  11. Press with left mouse button on OK button to return to Excel.

Color even months

Conditional formatting formula:


The NOT function returns the boolean opposite to the given argument. The numerical equivalent to boolean value TRUE is 1 and FALSE is 0 (zero).

Highlight odd even months2

Color odd years

Conditional formatting formula:


The YEAR function returns the year based on a date.

Color even years

Conditional formatting formula:


Border between months1

Border between months

Conditional formatting formula:


Border between months2

Border between years

Conditional formatting formula:


Get excel file for this tutorial.

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