Here is a picture of a simple calendar.

I have used conditional formatting to:

• highlight date ranges (green)
• highlight possible overlapping dates  (red)
• not exisiting dates (black)

Here are the date ranges:

How I created "invisible" dates in cell range B3:AF14

1. Formula in B3: =DATE($D$1;ROWS($A$3:$A3);B$2) + ENTER.
   $D$1 is an absolute reference to a cell containing a year.
2. Copy cell B3 and paste into cell range B3:AF14.
3. Select B3:AF14
4. Press CTRL + 1
5. Select "Number" tab
6. Select "Custom"
7. Type ,,, in "Type:" window
8. Click OK!

How to highlight date ranges green (conditional formatting)

1. Select B3:AF14.
2. Click "Home" tab
3. Click "Conditional formatting" button
4. Click "New Rule.."
5. Select "Use a formula to determine which cells to format"
6. Type in "Format values where this formula is true" window: =SUMPRODUCT((B3<=$I$19:$I$26)*(B3>=$E$19:$E$26))=1
7. Click "Format.." button
8. Click "Fill" tab
9. Select a color (green)
10. Click OK!
11. Click OK!
12. Click OK!

How to highlight overlapping date ranges red (conditional formatting)

1. Select B3:AF14.
2. Click "Home" tab
3. Click "Conditional formatting" button
4. Click "New Rule.."
5. Select "Use a formula to determine which cells to format"
6. Type in "Format values where this formula is true" window: =SUMPRODUCT((B3<=$I$19:$I$26)*(B3>=$E$19:$E$26))>1
7. Click "Format.." button
8. Click "Fill" tab
9. Select a color (red)
10. Click OK!
11. Click OK!
12. Click OK!

How to highlight not existing dates black (conditional formatting)

1. Select B3:AF14.
2. Click "Home" tab
3. Click "Conditional formatting" button
4. Click "New Rule.."
5. Select "Use a formula to determine which cells to format"
6. Type in "Format values where this formula is true" window:=MONTH(B3)<>ROWS($B$3:$B3)
7. Click "Format.." button
8. Click "Fill" tab
9. Select a color (black)
10. Click OK!
11. Click OK!
12. Click OK!

How to reorder conditional formatting rules

1. Click "Home" tab
2. Click "Conditional formatting" button
3. Click "Manage Rules.."
4. Reorder rules using arrow buttons

Rule (applied in the order shown)

• Black
• Red
• Green

How these excel formulae work

You will find an explanation here:
Highlight overlapping date ranges using conditional formatting in excel

Download excel sample file for this tutorial.

Visualize date ranges.xls
(Excel 97-2003 Workbook *.xls)

Related articles:

Find overlapping date ranges in excel

Highlight overlapping date ranges using conditional formatting in excel

Calendar with scheduling in excel 2007 (vba)

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

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

ROWS(array) returns the number of rows in a reference or an array