Heat map yearly calendar
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional formatting formulas and an Excel defined table that allows you to add new events or edit/delete old ones.
What you perhaps want to customize is how events are highlighted. The following table shows how conditional formatting is used.
Event frequency  CF formula 
1  =SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))=1 
2  =SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))=2 
3  =SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))=3 
4  =SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))=4 
This image shows the rules manager for conditional formatting formulas, it shows you the fill color I used and the calendar cell range it is applied to.
Explaining CF formula
The following CF formula is the one that identifies dates that have only one event scheduled.
Step 1  Check date in calendar with start column in Excel table
Check if date in cell B6 is larger than or equal to start dates. The INDIRECT function is needed to be able to use an Excel table name in a Conditional Formatting formula.
The INT function removes the decimal part of the date and time value.
INT(INDIRECT("Table1[Start]"))<=B6
becomes
INT({41282.375; 41330.3333333333; 41312.3333333333; 41351.4166666667; 41365.5; 41421.75; 41438.3333333333; 41448.625; 41283.3333333333; 41283.3333333333; 41312.3333333333; 41313.3333333333; 41314.3333333333; 41357.4166666667; 41366.5; 41408.75; 41408.75; 41449.75; 41462.75; 41472.75; 41613; 41477; 41478; 41479})<=B6
becomes
{41282; 41330; 41312; 41351; 41365; 41421; 41438; 41448; 41283; 41283; 41312; 41313; 41314; 41357; 41366; 41408; 41408; 41449; 41462; 41472; 41613; 41477; 41478; 41479}<=41273
and returns
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Step 2  Check date in calendar with end column in Excel table
INT(INDIRECT("Table1[End]"))>=B6))
returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
Step 3  Multiply conditions
In order to identify a date inside an event range both conditions must be met.
(INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6)
becomes
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE} * {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
and returns
{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.
Step 4  Sum events
The SUMPRODUCT function adds the numbers in the array and returns a total.
SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))
becomes
SUMPRODUCT({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})
and returns 0 (zero).
Step 5  Check if the number of events equal 1
If the sum is equal to 1 then the Conditional formatting formula returns TRUE and the cell is highlighted, if not then the formula returns FALSE and the cell is not highlighted.
SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))=1
becomes
0=1
and returns FALSE. Cell B6 is not highlighted.
Excel defined table
The following picture shows the Excel defined table containing the data about each event, the event name, when it starts and when it ends.
CF formula examples
If you want to highlight cells that have less then 5 events scheduled with a given color then use this CF formula:
If you want to highlight cells that have greater then 5 events and less than 10 events scheduled with a given color then use this CF formula:
There are also CF formulas that hide dates and formatting on each month because some cells show the previous or the next months dates. The calendar looks cleaner without them.
Download Excel *.xlsx
Note that no VBA is used in this workbook, however, there is a VBA solution demonstrated below if you prefer that.
Heat map  VBA Solution
Hi, I would like to use this example with my dataset, however, I'd like to visually show the number of events per date to understand when are we the busiest, slowest, etc. and be able to forecast using this data.
Ideally, I would like some sort of data bar or color change indicating the level for each date (Jan 1 has 10 items while Jan 2 has 3 and I can visually see that in each cell instead of seeing numbers or a solid color for each cell (here yellow and blue).
Answer:
This article demonstrates how to highlight events on a yearly calendar based on frequency per day. You will find a link to this workbook at the end of this article.
The color on the calendar gives a rough estimate on the number of events per date.
 No color no events.
 Light color one or a few events.
 Darker color means many events.
You add, edit or delete events to worksheet "Table" and every time you go back to worksheet "Calendar" the colors are refreshed by the macro below.
There is a specific cell next to the calendar that allows you to change the highlight color if you prefer. Click on that cell and change the cell color to a color you want.
VBA code
 Right click on sheet Calendar

Click "View Code"
 Copy vba code below
 Paste code to sheet module
 Exit VB Editor
Private Sub Worksheet_Activate() Dim CRng As Variant Dim Dt As Variant Dim CDt As Variant Dim Cnt As Integer Dim r As Long Dim c As Long Dim St As Integer Application.ScreenUpdating = False CRng = Worksheets("Calendar").Range("B6:X38").Value With Worksheets("Table") For r = 1 To UBound(CRng, 1) For c = 1 To UBound(CRng, 2) If CRng(r, c) <> "" Then For CDt = 1 To .Range("Table1[Start]").Cells.Count If CRng(r, c) >= Int(.Range("Table1[Start]").Cells(CDt).Value) And CRng(r, c) <= Int(.Range("Table1[End]").Cells(CDt).Value) Then Cnt = Cnt + 1 End If Next CDt End If If Cnt > St Then St = Cnt Cnt = 0 Next c Next r End With Set Rng = Worksheets("Calendar").Range("B6:X38") 'Remove previous formatting With Rng.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Worksheets("Table") For Each Dt In Worksheets("Calendar").Range("B6:X38") For CDt = 1 To .Range("Table1[Start]").Cells.Count If Dt >= Int(.Range("Table1[Start]").Cells(CDt).Value) And Dt <= Int(.Range("Table1[End]").Cells(CDt).Value) Then Cnt = Cnt + 1 End If Next CDt If Cnt > 0 Then With Dt.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = Worksheets("Calendar").Range("AB5").Interior.Color ' xlThemeColorAccent4 ' .TintAndShade = 1  (Cnt / St) .PatternTintAndShade = 0 End With 'Reset counter a Cnt = 0 End If Next Dt End With Application.ScreenUpdating = True End Sub
WorkSheet "Table"
The picture below shows the events in an Excel defined table named [Table1].
You don't need to adjust cell references or formulas everything is automatic, Excel defined Tables are great in that aspect.
You can find a heat map monthly calendar here.
Download excel *.xlsm file
This Gantt chart uses a stacked bar chart to display the tasks and their corresponding date ranges. Completed days are [โฆ]
This chart is an arrow chart that has horizontal and vertical lines, positive arrows are green and negative arrows are [โฆ]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The [โฆ]
Removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to [โฆ]
Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe [โฆ]
Plot date ranges in a calendar part 2
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names [โฆ]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The xaxis is comprised of [โฆ]
18 Responses to โHeat map yearly 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.
Contact Oscar
You can contact me through this contact form
Hi Oscar,
I love this calendar, but I have a couple of questions about it,
1) Is there any way of having the Table appearing alongside the calendar so you don't have to flick between the two sheets each time you want to add something.
2) Say I selected cell Calendar! N7 (07/02/13), is there any way of highlighting events 3 and 11 on the table which affect this date.
Thanks,
Chris
Chris G,
Great comment!
Click to view larger.
Download excel *.xlsm file
Heatmapcalendarv2.xlsm
Hi Oscar,
I've downloaded the file for the second version, the table itself is visable, along with the refresh calendar option and it's working great, however it isn't highlighting the table like it does in the screenshot. How do I get it to highlight the related events for the date?
Thanks,
Chris
Chris G,
That is weird. I downloaded the file and it is working here.
The workbook contains some code in the sheet module:
Right click on sheet name and click "View Code". The code should be there?
The sheet also has a conditional formatting formula applied to the table:
=(INT(INDIRECT("Table1[@Start]"))<=$AD$4) *(INT(INDIRECT("Table1[@End]"))>=$AD$4)
How about a Sparkline (single barchart) that fills the cell/background up to xx%.
//Ola
Ola,
I donยดt think that is possible. Conditional format  Data bars uses the cell value and you canยดt enter your own conditional formatting formula.
Hi Oscar ,
I am using Excel 2007 , and I had the same problem as Chris ; the CF coloring of the events table was not working.
I changed the CF formula to :
=(INT($AB7)=$AD$4)
after selecting the entire events table AA7:AC27.
Everything works correctly now.
Hi ,
The formula in my earlier post has been changed by the website software ! What I had copied was :
=(INT($AB7)<=$AD$4)*(INT($AB7)>=$AD$4)
is equal to (INT($AB7) is less than or equal to $AD$4) multiplied by (INT($AC7) is greater than or equal to $AD$4).
K. Narayan,
Thanks for sharing a solution! I changed your last comment.
I am not sure why wordpress removes greater than or less than signs.
Oscar  hey i really like how this is formatted 
however, would it be possible to change the calendar so instead of looking at how many events are on a certain day  it looked at a number associated with a day (1100) and days with a high number are color coded red and days with a low number are color coded blue?
thanks!
Paul
Hi Oscar,
This is very useful but as i add more events i now get a Runtime error '5': Invalid procedure call or argument at the tint and shade line.
"with Dt.Interior
.TintandShade = 1 (Cnt / St)
"
i think it has to do with the value being greater than 1.
Is there an alternate to the calculate i could use.
Thanks in advance and thankyou for your articles as i have learnt alot from them.
Regards Tammy
Tammyw,
can you upload an example file?
Hi Oscar,
Sorry for delay but having great trouble with your website access.
This is the code i'm using to stop the error.
[If (Cnt / St) > 1 Then
.TintAndShade = 1  (Cnt / St) / 10
Else
.TintAndShade = 1  (Cnt / St)
End If]
i have 52 rows of 'Deliverables' and 12 columns of dates (Periods 1 to 12), therefore a large count number of dates.
This solution works well for me at the moment.
Thanks again for your interesting posts,i have learnt a great deal from them.
Tammyw,
Sorry for delay but having great trouble with your website access.
Yes, I have had some trouble with the server database.
This is the code i'm using to stop the error.
i have 52 rows of 'Deliverables' and 12 columns of dates (Periods 1 to 12), therefore a large count number of dates.
This solution works well for me at the moment.
Thanks for posting!
Thanks again for your interesting posts,i have learnt a great deal from them.
Thank you!
How would I go about creating a code to filter according to certain events? Fore example if I wanted to filter in order to just show Holidays.
Hello Oscar! This is a really good file, thanks a lot for sharing. I have no idea about how VBA works but i want to make a calendar like this to see a heat map of revenue received throughout the year.
In my "Table" i only need 2 columns:
1. Date from Jan 1 to Dec 31
2. Revenue on each day
How do i get this to reflect on the calendar?
Since it's unlike an event with a start and end date, i'm not sure how i can amend this. Thank you so much for your time.
Hi Oscar,
Thank you for sharing these very useful projects openly, I really appreciate that.
I am trying to lay down course schedules on a weekly calendar of all the first year courses, which could have shades of colors showing range of capacities for each schedule type (lectures, tutorials and labs). But there will be overlapping events. Every term, I am trying to find the void spots in student schedules to fit in my workshops to the gaps. This is how my data looks like:
Course Name Course Code Schedule Type Capacity Time Day
Chemistry II CHEM 1020U Lec 69 9:40 am  12:30 pm M
Chemistry II CHEM 1020U Lec 69 9:40 am  12:30 pm W
Chemistry II CHEM 1020U Lab 23 1:10 pm  4:00 pm R
Chemistry II CHEM 1020U Lab 23 9:10 am  12:00 pm F
Chemistry II CHEM 1020U Lab 23 1:10 pm  4:00 pm F
Introduction to Programming ENGR 1200U Lec 75 1:10 pm  4:00 pm T
Introduction to Programming ENGR 1200U Lec 75 1:10 pm  4:00 pm R
Introduction to Programming ENGR 1200U Tut 75 5:40 pm  7:30 pm M
Introduction to Programming ENGR 1200U Tut 75 5:40 pm  7:30 pm W
Calculus II MATH 1020U Lec 234 9:10 am  12:00 pm T
Calculus II MATH 1020U Lec 234 9:10 am  12:00 pm R
Calculus II MATH 1020U Tut 36 4:10 pm  7:00 pm T
Calculus II MATH 1020U Tut 36 4:10 pm  7:00 pm R
Calculus II MATH 1020U Tut 36 12:10 pm  3:00 pm F
Calculus II MATH 1020U Tut 36 9:10 am  12:00 pm F
Calculus II MATH 1020U Tut 36 4:10 pm  7:00 pm T
Calculus II MATH 1020U Tut 36 12:10 pm  3:00 pm F
Calculus II MATH 1020U Tut 18 9:10 am  12:00 pm F
Linear Algebra for Engineers MATH 1850U Lec 144 4:10 pm  7:00 pm T
Linear Algebra for Engineers MATH 1850U Lec 144 4:10 pm  7:00 pm R
Linear Algebra for Engineers MATH 1850U Tut 38 9:10 am  12:00 pm F
Linear Algebra for Engineers MATH 1850U Tut 38 12:10 pm  3:00 pm F
Linear Algebra for Engineers MATH 1850U Tut 38 9:10 am  12:00 pm F
Linear Algebra for Engineers MATH 1850U Tut 38 12:10 pm  3:00 pm F
Thanks in advance,
Eda
Eda Aydin,
Perhaps this weekly calendar will work for you?
https://www.getdigitalhelp.com/2010/02/26/calendarwithschedulinginexcel2007vba/