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

Color odd months

Conditional formatting formula:

=MOD(MONTH($B6),2)

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.

MONTH($B6)

becomes

MONTH(1/1/2008)

becomes

MONTH(39448)

and returns 1. 1 = January.

Step 2 - Calculate remainder

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

MOD(MONTH($B6),2)

becomes

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. Click on "Conditional Formatting" button
  4. Click 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 click on "Format..." button
  8. Go to tab "Fill"
  9. Pick a color
  10. Click OK button
  11. Click OK button to return to Excel.

Color even months

Conditional formatting formula:

=NOT(MOD(MONTH($B6),2))

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:

=MOD(YEAR($B6),2)

The YEAR function returns the year based on a date.

Color even years

Conditional formatting formula:

=NOT(MOD(YEAR($B6),2))

Border between months1

Border between months

Conditional formatting formula:

=MONTH($F7)<>MONTH($F6)

Border between months2

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)

Related articles

Highlight dates in a date range

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column [โ€ฆ]

How to highlight MAX and MIN value based on month

The image above shows rows highlighted based on value in column C being the largest or smallest in that particular [โ€ฆ]

Highlight date ranges overlapping selected record [VBA]

The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional [โ€ฆ]