## Visualize date ranges in a calendar in excel

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

- 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. - Copy cell B3 and paste into cell range B3:AF14.
- Select B3:AF14
- Press CTRL + 1
- Select "Number" tab
- Select "Custom"
- Type ,,, in "Type:" window
- Click OK!

### How to highlight date ranges green (conditional formatting)

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

### How to highlight overlapping date ranges red (conditional formatting)

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

### How to highlight not existing dates black (conditional formatting)

- Select B3:AF14.
- Click "Home" tab
- Click "Conditional formatting" button
- Click "New Rule.."
- Select "Use a formula to determine which cells to format"
- Type in "Format values where this formula is true" window:=MONTH(B3)<>ROWS($B$3:$B3)
- Click "Format.." button
- Click "Fill" tab
- Select a color (black)
- Click OK!
- Click OK!
- Click OK!

### How to reorder conditional formatting rules

- Click "Home" tab
- Click "Conditional formatting" button
- Click "Manage Rules.."
- 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

### 9 Responses to “Visualize date ranges in a calendar in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hi.

So this is exactly what i am looking for to get my Staff Leave Calendar going except that my table (data sheet) is on another worksheet in the same workbook as the calendar. How can i amend the formula so that it works for me?

Lorne,

Conditional formatting can´t reference cells in other sheets. There is a workaround, use named ranges.

Hi,

thank you for the excellent article. I have only one question. Can I use the same method if the chart looks like this - http://www.freeimagehosting.net/d5b2k I want to add the months (in yellow) on top of the days and get rid of the red months.

I will appreciate any help. Thank you!

[...] on Jul.27, 2012. Email This article to a Friend I have created a new version of Visualize date ranges in a calendar. This excel file let´s you enter names and date ranges (A20:G33). Duplicate names are allowed. [...]

Hi

This is really great thank you! It almost does exactly what I want/need.

The thing I'd love to be able to do is to start part way through one year, and end part way through the next - eg Start Oct 2014 and end the table at Sep 2015.

I've tried playing around with your sheet and I just cannot get it to do that.

For example how is your "invisible date" formula working out the month? It appears it can't be taking the letters "Jan" from A3, because if I simply change those to "Dec" then it still highlights the dates as though they are January.

How could I achieve what I want and have the table denoting the end of one year and the beginning of another?

Thanks again and all the best.

Hi,

I am using your formula, but instead of having the months downwards in colums I have an endless series.

It works, but for some reason it does not highlight the first day of some of the data range (two first ranges are not showing first date while third range works perfectly).

I have checked the invisible dates and they are correct. Do you have any idea as the reason why I am having this issue?

Cheers,

Jorunn

Hi again,

I figured it out. I used time in the date range (03.04.2015 06:00).

Do you know if there is a way wich makes you able to include the time without the effecting the vizualization?

Cheers

Jo,

Try this:

CF formula highlighting green cells:

=SUMPRODUCT((B3< =INT($I$20:$I$26))*(B3>=INT($E$20:$E$26)))=1

CF formula highlighting red cells:

=SUMPRODUCT((B3< =INT($I$20:$I$26))*(B3>=INT($E$20:$E$26)))>1

Many thanks for this, solved a problem I have been working on for a while. Is there any way of combining this conditional formating with another formula so that the cells are only formated if there is a certain name in the first column of the date ranges. I would like to format the dates with different colours for different people.

Many thanks