• open dock
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Contact Us
Change this Limited
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Contact Us

Date Functions : Excel Calendar (Part 2)

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.

Date_Functions_2_01

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.

Date_Functions_2_02

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

Date_Functions_2_03

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.

Date_Functions_2_04

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

Date_Functions_2_05

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.

Date_Functions_2_06

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

Date_Functions_2_07

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.

Date_Functions_2_08

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.

Date_Functions_2_09

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

Date Functions 2 (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.

  • 08 Mar
  • Tobias Owen
  • Excel
  •  1 Comment
  •  Like
  • Calendar , DATE() , Drop-down , Excel , Lookups , MATCH() , Named Range , Project Tracker

Share This

Related Posts

  • DATEDIF() : Excel’s Hidden Gem
  • Tax Year Excel Formula
  • Rank by Multiple Criteria in Excel
  • Calculate VAT in Excel
Date Functions : Excel Calendar (Part 1) →← Rock Paper Scissors
0

Comments

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Recent Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • Archives

    • December 2018
    • November 2017
    • June 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • September 2016
    • August 2016
    • July 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • September 2015
    • June 2015
    • May 2015
    • April 2015
  • Categories

    • Case Studies
    • Excel
    • Insight
    • News
    • Portfolio
    • Power BI
    • SAP Dashboards
    • SharePoint
  • Search our site

  • Social

  • Recent Blog Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • © 2016 Change this Limited
    Website by Change this
    Standard Terms of Business
    Privacy Policy

    Delivering the difference

This website uses some cookies to improve your experience within the website.


We also use cookies to understand how visitors use our website Accept


To find out what cookies we use and for more information, Read More.
Privacy & Cookies Policy

Necessary Always Enabled

Non-necessary