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. 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:

=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)

Get excel file for this tutorial.

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

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Find min and max unique and duplicate numerical values

Table of Contents Extract largest duplicate number Extract largest duplicate number - Excel 365 Extract smallest duplicate number Extract smallest […]

Match two criteria and return multiple records

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

Create a list of dates with blanks between quarters

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

How to use the LOGEST function

The LOGEST function returns an array of values representing the parameters of an exponential curve that fits your data, based […]

Repeat values across cells

This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]

How to use the EVALUATE method

The Evaluate method converts an Excel name to an object or value. The picture above demonstrates a macro that uses […]