Plot date ranges in a calendar
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.
Check out this article Heat map yearly calendar if you want to highlight date ranges in yearly view.
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
- 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.
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. - Select B3:AF14
- Press CTRL + 1
- Select "Number" tab
- Select "Custom"
- Type ,,, in "Type:" window
- Press with left mouse button on OK!
The steps above hide the dates in cell range B3:AF14.
How to highlight date ranges green
- Select B3:AF14.
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional formatting" button
- Press with left mouse button on "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
- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color (green)
- Press with left mouse button on OK!
- Press with left mouse button on OK!
- Press with left mouse button on 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.
- Select B3:AF14.
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional formatting" button
- Press with left mouse button on "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
- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color (red)
- Press with left mouse button on OK!
- Press with left mouse button on OK!
- Press with left mouse button on 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
- Select B3:AF14.
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional formatting" button
- Press with left mouse button on "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)
- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color (black)
- Press with left mouse button on OK!
- Press with left mouse button on OK!
- Press with left mouse button on 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
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional formatting" button
- Press with left mouse button on "Manage Rules.."
- 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
Calendar category
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
Excel categories
18 Responses to “Plot date ranges in a calendar”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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 - https://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
Hello,
Did you find a solution to this?
Hi,
Thanks for the formula this is almost what I was looking for, but is there a way to show the name of the person that has the range in the calendar?
So in the calendar the days 1 though 5 of February will have the name Theodor in the cells and so on?
Hi Oscar,
I am using your visualize overlapping date range part 2 template and when i am trying to change the range from B33 to B41 the template stops working I mean it does not work. Please help.
Is it possible to set conditional formatting to only highlight dates that overlap if there are more than 3 people for example that want vacation at the same time for overlapping dates?
Michelle,
Yes, it is possible.
You need to change two CF formulas.
Red:
=SUMPRODUCT((B3=$E$19:$E$26))>3
Green:
=(SUMPRODUCT((B3=$E$19:$E$26))<=3)*(SUMPRODUCT((B3=$E$19:$E$26))>=1)
Get the workbook:
Plot-date-ranges-in-a-worksheet-3-or-more.xlsx
I need to highlight Saturdays and Sundays with a different color. Can someone assist here?
BUENAS NOCHES Y MUCHAS GRACIAS, ME AYUDO MUCHO ESTA INFORMACION SOBRE PERSONALIZAR LA PLANTILLA DE CALELDARIO.
GRACIAS,..!!
Google translate: GOOD NIGHT AND THANK YOU VERY MUCH, THIS INFORMATION ON CUSTOMIZING THE CALENDAR TEMPLATE HELPED ME A LOT. THANK YOU,..!!
In first section not ; but ,
Good afternoon, this is amazing!
Is it possible to highlight the a particular date range (as above in green) but based on a name in another column?
Thanks a lot!