Highlight events in a yearly calendar

This article demonstrates how to highlight given date ranges in a yearly calendar, this calendar allows you to change the year and the calendar dates change accordingly.

The image above shows an Excel Table to the right of the calendar, you can easily add as many events as you like.

This worksheet uses Conditional Formatting formulas containing structured references pointing to the Excel defined Table, this makes it simple to use because no formulas need to be changed when the list of events grows larger.

Steven asks:
I got 6 events with different dates.
Event - DATE START - DATE END
1
2
3
4
5
6
On sheet 2 I have a Year Calander (365 Days). I need to do apply conditional formatting to highlight the days in which I have events.The six events are just a start and the list will grow longer. I want to have a pictorial view of the calendar on where the events fall in the year which is Sheet2.Sheet1 is just the key in the dates for the start and end.

How to create an Excel Table

The main benefit of converting the data set, in this case, is that the cell references in formulas don't need to be changed when you add additional date ranges to the Excel Table.

The cell references pointing to an Excel Table are called structured references and are dynamic, they don't change no matter how many date ranges you add to the Table.

One disadvantage with structured cell references is that you need to apply a workaround in order to use them in Conditional formatting formulas and Drop Down Lists.

Select any cell in your data set.
Press shortcut keys CTRL + T to open the "Create Table" dialog box, see image above.
Press OK button to create the Excel Table.

How to build a dynamic yearly calendar based on input year

Here are the steps to create the calendar without dates.

Select cell range B4:H4.
Go to tab "Home" on the ribbon if you are not already there.
Press with left mouse button on "Merge and Center" button.
Type this formula in cell B4: =DATE($K$2,1,1) and press Enter. This will return a date, however, we need only the month name to be displayed.
Select cell B4 and press CTRL + 1 to open the "Format Cells" dialog box.
Select category: Custom and type mmmm.
Press with left mouse button on OK button. This will format the date in cell B4 to only show the month name. This will make the month name dynamic meaning it will change if the Excel user has a different Excel language installed.
Select cell B5 and type Mo and then press Tab key to move to the next cell.
Continue typing Tu, We, Th, Fr, Sa and Su with the remaining cells, see image above.
Press and hold on column header B.
Drag with mouse to column H.
Press and hold on any of the separating lines between the column headers.
Drag with mouse until column width is around 26 pixels, you can change this later.
Release mouse button and all selected columns will have the width 26 pixels.
Copy cell range B4:H5 and paste to J4:P5.
Change columns widths to 26 pixels.
Enter this formula in cell J4 for February: =DATE($K$2,2,1)
The only difference between this formula and the formula for January is the month argument which I have bolded in the formula above.
2 represents February which is the second month.
Copy cell range B4:X5 and paste to B13:X20.
Change these months as well. March formula: =DATE($K$2,3,1)
Repeat with remaining quarters.

The week starts with Sunday if you live in the US, the image then looks like this.

Select month names, weekday names and six rows below each month and apply a border to the selected cells.

Go to tab "Home" on the ribbon.
Press with mouse on border button.
Press with mouse on "All borders".

This creates a border around each cell.

Select columns B to X.
Go to tab "Home" on the ribbon.
Press with mouse on "Center" button to center cell content.

Calendar date formulas

I center and merged cell range K2:O2 and entered year 2020 as an example, all formulas will be based on this year that is entered in cell K2.

Select cell B6 which is the first cell for the month of January, type the following formula:

=DATE($K$2,1,1)-WEEKDAY(DATE($K$2,1,1),2)+1

This formula calculates the first date in the first week which the first day in January falls, this may be a date in December, however, I will use Conditional formatting to hide dates outside the month later in this article.

The DATE function uses three arguments, year, month and day. DATE(year, month, day)

DATE($K$2,1,1)

becomes

DATE(2020,1,1)

and returns 12/30/2019.

The WEEKDAY function calculates a number based on a date representing the position in a week. WEEKDAY(serial_number,[return_type])

The serial_number argument is the date and the [return_type] argument lets you pick which day the week begins with. return_type 2 returns 1 for Monday, 2 for Tuesday, 3 for Wednesday, etc.

DATE($K$2,1,1)-WEEKDAY(DATE($K$2,1,1),2)+1

becomes

43831-WEEKDAY(43831,2)+1

1/1/2020 falls on a Wednesday and the WEEKNUM function will then return 3. 3 = Wednesday.

43831-3

and returns

43828 which is 12/29/2019.

Copy cell B6 and paste to the first cell in the remaining months, change the number representing the month argument in the formula so it corresponds to the month.

For example, in February the formula becomes:

=DATE($K$2,2,1)-WEEKDAY(DATE($K$2,2,1),2)+1

2 represents February and is bolded in the formula above.

Go back to month January and enter this formula in cell C6:

=B6+1

Copy cell C6 and paste formula to cell range C6:H11. Enter the following formula in cell B7:

=B7+7

Copy cell B7 and paste to cell range B8:B11, month January is now finished. Repeat above steps with the remaining months.

Hide dates

The image above shows the calendar, however, dates that don't belong to the month are also displayed. This may or may not be what you want, you can hide them using Conditional Formatting or color them differently also using Conditional Formatting.

Select cell range B6:H11, go to tab "Home" on the ribbon. Press with mouse on the "Conditional Formatting" button and then press with left mouse button on "New Rule...", this opens a dialog box.

Press with mouse on "Use a formula to determine which cells to format", then type this formula:

=MONTH(B6)<>1

Press with mouse on the "Format..." button and a "Format Cells" dialog box shows up. Press with mouse on tab "Font".

Pick font color white, this will make the text hidden. White font against a white background and the entire cell will be white.

Press with left mouse button on OK button and press with left mouse button on the next OK button as well. Then press with left mouse button on "Apply" button.

If you want the dates to be shown but not as prominent, use a grey color instead.

Apply the same conditional formatting formula to the remaining months, however, change the number so it represents the month number.

For example, Februarys CF formula becomes:

=MONTH(B6)<>2

Highlight date ranges in calendar

I will now demonstrate how to apply Conditional Formatting in order to highlight dates based on the events specified in the Excel Table. If you change the year in cell K2 the highlighted dates will change accordingly making the calendar dynamic.

Select all dates in the calendar. Tip! Press and CTRL key and then select the cell ranges. For example. the cell ranges to be selected in the first quarter are B6:H11, J6:P11 and R6:X11.
Go to tab "Home" on the ribbon.
Press with left mouse button on the "Conditional Formatting" button.
Press with left mouse button on "New Rule..."
Press with left mouse button on "Use a formula to determine which cells to format".
Type this formula: =IF(B6="",FALSE,SUMPRODUCT((B6>=INDIRECT("Table1[Start]"))*(B6<=INDIRECT("Table1[End]"))))
Press with left mouse button on "Format..." button.
Press with left mouse button on tab "Fill"
Pick a color.
Press with left mouse button on OK button.
Press with left mouse button on OK button.
Select the CF formula you just now created, press with left mouse button on the arrow keys to move the formula to the bottom of the list.
Press with left mouse button on all checkboxes "Stop If True" so that the last CF formula won't be rund if any of the other are. This will prevent hidden dates from being highlighted. See image above.
Press with left mouse button on Apply button and then OK button.

How to change year

You can change the year in cell K2 and the calendar changes almost instantly.

How to add or remove events

The events are in an excel defined table. You can add or remove rows by press with right mouse button oning on a cell and select Insert or Delete.

You can also add a blank row by selecting the last cell in the table.

Press Tab key.

You can move the table to any sheet you like.

Get the Excel file

Highlight-events-in-a-calendarv4.xlsx

Calendar category

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

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

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

See all articles in 'calendar' category

Templates category

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

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

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

See all articles in 'templates' category

Functions in this article

DATE MONTH

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

12 Responses to “Highlight events in a yearly calendar”

Peter Clark says:

January 27, 2013 at 12:16 am

Hi,
There are several bugs in your calendar. The dates are not quite right for 2013, and I think this is because of the parameter chosen in the Weekday function.
Also, when the month starts on a Sunday, it drops down a line. I tried to fix this by changing the functions but then the Sunday was blanked and I didn't have time to unravel your conditional formatting.

regardless, this is very useful for me so thanks
Peter

Reply
- Oscar says:
  
  January 27, 2013 at 10:56 am
  
  Peter Clark,
  
  thanks!
  
  There are several bugs in your calendar. The dates are not quite right for 2013, and I think this is because of the parameter chosen in the Weekday function.
  
  Corrected!
  
  Also, when the month starts on a Sunday, it drops down a line.
  
  I am not seeing this on my sheet.
  
  Thanks for commenting!
G. Cooley says:

May 13, 2013 at 1:44 pm

Hi.
I have a worksheet that generates a schedule of milestone dates via formula after I type in one date. I use your calendars on different worksheets and I pull in the dates from my main sheet to insert into the table that you use to drive the calendars (only I have the start and end date the same since they are milestones-- only one day is highlighted on the calendar, not a range of days.) I then use Excel's camera utility to take an image of the calendar and put it on my main screen next to the generated dates.

The problem I'm having is when I create a second set of worksheets in the same workbook, the new calendars still want to show the values that were on the first set of worksheets, even though I've changed the pointers so they point to the dates on the new main sheet. Example: on the first set that worked-- the formula for the first start date in your calendar's table is:

='Nov 2013 Schedule Calculator'!E3

On my new set, the formula for the first start date in your calendar's table is:

='Aug 2013 Schedule Calculator'!E3

So the dates in the table are correct-- but the calendar seems to be getting its instructions on what to highlight from elsewhere.

How do I adjust this?

Reply
- Oscar says:
  
  May 14, 2013 at 1:56 pm
  
  G. Cooley,
  
  1. Select the picture in the second set of worksheets
  2. Change the cell reference in the formula bar
  
  3. Press Enter
G. Cooley says:

May 21, 2013 at 12:16 am

Oscar-- thanks, that fixed it. This is great software.

I just have one last question. I did change the color from the gray to red so it stands out, and I'm using the same start and end date because I'm showing milestones, not ranges. It works fine, but I have a date-- Sept 4, 2013. It shows up in red on Sept 4, but it also shows up in as the red 4 in the bottom of the August block (in the area that is supposed to be white-one-white). Do you know what is causing that?

G. Cooley

Reply
G. Cooley says:

May 21, 2013 at 6:47 pm

Oscar: I think I may have figured this out. On your rule for formula IF(MONTH (J24)8,1,0) for August, you have the Format set to white lettering on a NO BACKGROUND. All your other conditional formatting rules like this have the format set to white lettering on a WHITE background. I changed it and this seems to have fixed the problem. (without this fix, if you have days in the table in that first week of September, they show up at the bottom of the August month-- in that row that is supposed to be white on white.

G. Cooley

Reply
Robert says:

July 4, 2013 at 4:59 pm

We are using your calendar for our machining work centers. One question though, is there a way to have multiple tabs (example, one tab would be grinding, one tab would be machining etc...)

I tried just copying the tab, but that doesn't seem to work.

Thanks,
Rob

Reply
- Oscar says:
  
  July 8, 2013 at 1:55 pm
  
  Robert,
  
  is there a way to have multiple tabs (example, one tab would be grinding, one tab would be machining
  
  Yes!
  
  1. Copy sheet
  2. Select the new sheet
  3. Find out the new table name (Select a cell in the table and press with left mouse button on tab "Design")
  4. Change this conditional formatting formula, replace Table1 with the new table name (Table2):
  
  =IF(B6="",FALSE,SUMPRODUCT((B6>=INDIRECT("Table1[Start]"))*(B6<=INDIRECT("Table1[End]")))) to =IF(B6="",FALSE,SUMPRODUCT((B6>=INDIRECT("Table2[Start]"))*(B6<=INDIRECT("Table2[End]"))))
Farmer Kzhel says:

January 8, 2016 at 4:25 pm

Is it possible to display Event name in calendar instead of highlight or first three letters of event

Reply
Aaron says:

February 26, 2016 at 5:50 pm

Great job on this. 2 quick questions.

1. Is there a way to make each event a different color?
2. Is there a way to make it so that if 2 event dates overlap that it makes the overlapping calendar dates highlighted red?

I will dig into it and figure it out but just thought you may have a quick answer.

Thanks!

Reply
- Hossein says:
  
  July 24, 2022 at 8:06 pm
  
  I have the same question. Please let me know if you have figured this out.
  
  Thanks,
  Hossein.
yunus says:

January 18, 2018 at 4:13 pm

when you set event1 date 1.1.2013 to 31.12.2013, you can see
august has a problem with coloring.

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