Scenario
This blog builds upon the custom calendar that was created as part of my previous Excel Calendar blog. Using the monthly template that was created, we wanted to build a dynamic calendar that would highlight key dates for each selected month.
Setting up the drop-down menu
The template we created previously required you to type in the first date of the month in the header bar, which would then display the full name of the month. Whilst it serves its purpose, this method is a little clunky, so we are going to set up the calendar to be populated by a drop-down menu instead.
Start by inserting a new header row above the existing calendar template (it must be above, to preserve the formula references later on). Split this into two sections, as below, by merging the cells.
Also, create a new tab called “Lookups”. Whilst not essential, I always prefer to have my lookups separate from the “front end” of my spreadsheet.
We are going to set up this new header bar to be populated via a drop-down menu. We must first, therefore, set up the menu options.
Create a new list on the Lookups tab containing the months of the year. Then, using the menu, navigate to Formulas → Name Manager (as below). Click “New…” to set up a new named range.
Name this range “Month” and click OK. Close the Name Manager dialogue box.
Return to the “Calendar” tab and select cell B2 (which should now be part of the newly-created header bar). Using the menu, navigate to Data → Data Validation (as below). Under “Allow”, select “List”. Under “Source”, we want to point to the named range we just created. We can do this by typing “=Month”.
Selecting cell B2 will now enable you to use a drop-down menu to select a month, but it doesn’t yet affect the calendar template. For now, select “April” and type “2016” into cell G2, so that the target month and year are displayed in the header bar.
As with the original template, cell B3 will display the 1st date of the month. This is required by the formulas in the main part of the calendar. In order for this cell to respond to a user’s selection in the drop-down menu, it must build a date from the following information:-
• The year in cell G2
• The month in cell B2
• Day 1 (i.e. the 1st of the month)
This can be achieved using a veritable cocktail of Excel functions, which will work together to deliver the functionality we need. We will use the DATE(), MONTH() and LEFT() functions.
On its own the DATE() function simply requires a year, month number and day number to display the required date. E.g. The formula =DATE(2016,4,1) would display 01/04/2016.
For the purposes of this calendar, the year will always be referenced in cell G2 and the day number will always be 1. The month number is trickier to obtain, but can be done with a little-known Excel exploit.
The MONTH() function looks at any date cell and returns the number of the month in the date (see below). This function will read any Excel date format, including the shorthand dd-mmm format (e.g. 01-Apr). We can translate the month name selected in cell B2 into this format using a combination of concatenation and the LEFT() function.
The LEFT() function looks at any text string and returns the specified number of leftmost characters from the text string. In the example below, it is returning the 3 leftmost characters from cell B2.
Using the concatenation =”01-“&LEFT(B2,3) will therefore give us the 01-Apr notation that the MONTH() function requires to output the month number. When used as part of the DATE() function, this yields the following formula:-
Cell B3 will now always display the first date of the month that has been selected via the drop-down menu in cell B2. The main part of the calendar, which references cell B3, will also respond to the selected month.
Since cell B3 is now only performing a back-end function, it does not need to be visible on the calendar. Hide row 3 of the spreadsheet to make the calendar a bit tidier.
Populating key events
The desired outcome here is to have a calendar that highlights key dates when a month is selected. In order to do this, we must set up the key dates themselves.
Set up two columns on the “Lookups” tab for key dates and descriptions. In this example, I have entered the dates of UK bank holidays in 2016 and a few generic meeting dates in April and May.
Select the full range of cells in the first column of the table (in this example, column C). Set up a named range in the same way that we did earlier using Name Manager. Name this range “KeyDates” and click OK (note that named ranges cannot contain spaces).
The rest is done in conditional formatting.
Return to the “Calendar” tab. Select the main part of the calendar (in this example, cells B5:H10). Using the menu, navigate to Home → Conditional Formatting and select “New Rule” from the menu. When the dialogue box appears, select the last option (Use a formula to define which cells to format).
We want to tell Excel to highlight any cell where the date appears in the “Key Dates” list. This can be done using the following formula (note that cell B5 is the first cell in the calendar, so is the cell referenced in the conditional formatting rule):-
=MATCH(B5,KeyDates,0)
Format the cells to turn blue, and click OK. The two meeting dates in April will now be highlighted on the calendar.
The conditional formatting will adjust itself for each month selected on the calendar. For example, selecting May from the drop-down menu highlights 3 dates. However, it’s not immediately obvious what events are taking place on these dates. To make this clearer, there is one more conditional formatting rule we can apply.
The MONTH() function can be used again, this time in the table of Key Dates and Events to identify which of the key dates fall in the currently selected month.
Navigate to the “Lookups” tab. Select the table of Key Dates and Events, and open the Conditional Formatting dialogue box. Once again, select the bottom option and type the following formula:-
=MONTH($C2)=MONTH(Calendar!$B$3)
Note that the reference to column C is locked, because this is the column that contains the dates. The formula references cell B3 on the “Calendar” tab, which is on the hidden row and populated with the first date of the selected month.
Select the same blue highlight as used previously and click OK. All of the dates that appear in May now appear highlighted in blue, allowing users to quickly check which events the highlighted cells on the calendar relate to.
There are many possible applications of this functionality, but one particularly useful application would be as a project tracker that would highlight when deadlines and other key dates are approaching.
Download The Workbook
Get The Formula
The full formulas used in this blog are as follows:-
=DATE(G2,MONTH("01-"&LEFT(B2,3)),1)
Translates the full name of the month in cell B2 into a date reference, and uses this to populate the cell with the first date in the month.
=MATCH(B5,KeyDates,0)
A conditional formatting rule that highlights a cell in the calendar if its date appears in the list of key dates (a named range).
=MONTH($C2)=MONTH(Calendar!$B$3)
A conditional formatting rule that highlights a row in the Key Dates and Events table if the key date falls in the select month.
angus
thanks a lot – saved me a load of time to work it our for myself 🙂