## 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.

### Table of Contents

Conditional formatting + Excel Table

- Conditional formatting formulas
- Explaining CF formula
- Populating the Excel table
- CF formula examples
- Get Excel *.xlsx

Macro + Excel Table

## 1. Conditional formatting formulas

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.

## 2. 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

The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference.

Function syntax: INDIRECT(ref_text, [a1])

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 from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.

Function syntax: INT(number)

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 (AND logic)

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

Function syntax:

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.

## 3. Populating the Excel 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.

## 4. 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.

## 5. Get Excel *.xlsx

Note that no VBA is used in this workbook, however, there is a VBA solution demonstrated below if you prefer that.

## 6. 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. Press with mouse on that cell and change the cell color to a color you want.

## 7. VBA code

- Press with right mouse button on on sheet Calendar
- Press with left mouse button on "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.

## 8. Get excel *.xlsm file

### Calendar category

This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]

I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

### Custom charts category

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 […]

I found a chart that I wanted to show you how to build. It contains values both horizontally and vertically, […]

### Templates category

This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]

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 x-axis is comprised of […]

### Functions in this article

More than 1300 Excel formulas

## Excel categories

### 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!

Press with left mouse button on image to view larger version.

Get the Excel *.xlsm file

Heat-map-calendar-v2.xlsm

Hi Oscar,

I've opened the attached file for the second version, the table itself is visible, 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 opened the file and it is working here.

The workbook contains some code in the sheet module:

Press with right mouse button on sheet name and press with left mouse button on "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 (1-100) 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 Run-time 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.get-digital-help.com/2010/02/26/calendar-with-scheduling-in-excel-2007-vba/