## 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 template is a stacked bar chart and new tasks are added as you type them. Completed (green) […]

I found a chart that I wanted to show you how to build. The printable planting guide is found here. This is […]

This calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe […]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

Plot date ranges in a calendar part 2

I have created a new version of Visualize date ranges in a calendar. This excel file let´s you enter names […]

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

Use the img tag, like this: <img src="Insert pic link here">

**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

Heat-map-calendar-v2.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 (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.