Highlight odd even months1

Color odd months

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

Color even months

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

Highlight odd even months2

Color odd years

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

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)

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)

  • Share/Bookmark

Related posts:

  1. Highlight dates within a date range using conditional formatting
  2. Highlight duplicates using conditional formatting in excel
  3. Highlight the second or more duplicates in two lists using conditional formatting in excel
  4. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
  5. Create a dynamic border to your list using excel conditional formatting
  6. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
  7. Highlight common values in two lists using conditional formatting in excel
  8. Highlight smallest duplicate value in a column using conditional formatting in excel
  9. Highlight duplicate values in a range using conditional formatting in excel
  10. Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel