Author: Oscar Cronquist Article last updated on July 14, 2018

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges.

The calendar lets you choose year in cell D1 and the highlighted cells changes accordingly and immediately. The months are in column A and days of the days of the months are in row 2.

This post Yet another excel calendar has a different layout yet still showing all dates in a year. There is also a monthly and daily view, however it won't allow you to add date ranges only single day events.

The following calendar Calendar – monthly view plots the events in a day instead of highlighting days.

I have used Conditional Formatting to

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

demonstrated in the image to the right.

The conditional formatting formula updates as you type new date ranges or edit an existing one in the table.

This gives you an overview of the date ranges that lets you easily spot any issues or errors.

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.

    Excel tries to output the entire date but the cell width is too small. That is why it looks like it does, in the image above.
  3. Select B3:AF14
  4. Press CTRL + 1
  5. Select "Number" tab
  6. Select "Custom"
  7. Type ,,, in "Type:" window
  8. Click OK!

The steps above hide the dates in cell range B3:AF14.

How to highlight date ranges green

  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!

The CF formula in step 6 above checks if the date is inside any of the date ranges, if exactly one date range is then the date cell is highlighted green.

How to highlight overlapping date ranges red

The steps here are identical to the steps above, however, the CF formula is different.

  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!

The CF formula in step 6 above checks if the date is inside any of the date ranges, if more than one date ranges are then the date cell is highlighted red.

How to highlight not existing dates black

  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!

The formula in step 6 above checks that the month number is equal to the row number returned by the ROWS function. This makes sure that the date is in the correct month and not in the next month.

If it is in the next month then the cell is colored black. The following image shows the days of the months. For example, February 2010 has 28 days, the formula starts with 1 March in the next cell to the right of 28.

To hide that the cell is colored black.

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

Conditional Formatting formulas

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

Download Excel *.xlsx file

Plot date ranges in a worksheet.xlsx