• 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 1)

Scenario

Excel doesn’t have a built-in calendar feature, but it comes with all the tools for us to build our own. In a later blog, I will explore how to populate a calendar so that days where events are taking place become highlighted. Firstly, though, we need to create the calendar.

Start off by setting up a template for your calendar month, as below. Some months can span six weeks, so ensure that your template has six rows.

Date_Functions_01

Note that Sunday must be the first day in row 3 because the WEEKDAY() function, which we’re going to use later, identifies Sunday as the first day of the week.

Next, type the first date of the month you wish to display in cell B2 (this should be a merged cell). For example, to display the month of April 2016, type 01/04/2016.

 

WEEKDAY() Function

We now need to tell our template which day of the week is the first of the month. For this, we will use the WEEKDAY() function.

WEEKDAY() references any date cell and returns a number that equates to a day of the week (assuming Sunday is day 1, Monday is day 2, etc.). In the example below, the function is returning a value of 6, which tells us that the 1st April 2016 is a Friday.

Date_Functions_02

We can use the WEEKDAY() function in a formula with an IF() statement to return a positive value if the first day of the month matches the weekday in the column header.

In the example below, the formula is checking whether the date in cell B2 is a Sunday (i.e. =1) and returning a Yes/No outcome.

Date_Functions_03

However, we don’t want a Yes/No outcome for our formula – we want our formula to check whether the first day of the month matches the column header, and if so, populate the cell with the date in cell B2 (i.e. the first of the month).

In order to be able to drag the formula across the whole of the first row, we can’t use the ‘=1’ condition as part of the WEEKDAY() function, as this will cause the formula to only check whether the date falls on a Sunday. We can instead use the COLUMNS() function, with a range that is locked at the start and unlocked at the end. This will cause the WEEKDAY() check to increase by 1 in each column to the right.

In the example below, the formula has identified that the 1st of April 2016 falls on a Friday, and has thus populated cell G4 with an outcome (note that the cell width is preventing the date from being displayed correctly). All of the other cells appear blank.

Date_Functions_04

In order for the date to be displayed correctly in cell G4 (and on the rest of the calendar), we must format the text accordingly. In this instance, we only want to show the number of the day (e.g. “1”). Since this is not one of the default number format options in Excel, we must enter a custom format.

Select the whole calendar range and navigate to ‘Number Formats’. This can be found under the ‘Home’ menu tab, as below:

Date_Functions_05

Navigate to ‘Number’ and then ‘Custom’. To display only the day number, we need to use simply the notation “d” (as below). If it does not already exist in your list, create it and click “OK”.

Date_Functions_06

The date value in cell G4 should now be displayed simply as ‘1’. The first row is now almost complete, but it’s not quite right – cell H4 needs to be displaying the second date of the month.

Since the WEEKDAY() function is only checking for the first date of the month, we will need another function that will check whether the cell to the left has already been populated, and if it has, add 1. This can be achieved with a nested IF() statement, as below:

Date_Functions_07

Drag this formula across the whole of the first row.

The second row is MUCH easier to set up. Since the first day of the month will always fall on one of the cells in the first row, we do not need the WEEKDAY() function checking for it in the second row and beyond.

This row simply needs to look at the previous cell (in most cases, the cell immediately to the left – but note the first cell of the second row, which will need to reference cell H4) and add 1.

Date_Functions_08

Keeping the cell references unlocked allows you to drag the formulas all the way down, and they will automatically reference the right cells, resulting in a calendar month full of dates.

However, in our example, when we reach the end of April 2016, the dates from May 2016 start to appear at the bottom of the calendar. This is because we haven’t yet told Excel to stop when we reach the end of the month.

 

EOMONTH() Function

There is a function that can help us with this. The EOMONTH() function references any date cell and returns the last date in the month – or the last date of ‘x’ months in the future. Since we only want to know the last date in the current month, we use the parameter ‘0’, as below:

Date_Functions_09

In this example, the formula in cell J8 is referencing the date in cell B2 and returning the last date of the month.

We can use this to instruct Excel: “If the date in the previous cell was the last date in the month, display an empty cell, otherwise add 1 to the previous date”.

Since the last day of the month will only ever fall on the final two rows of the calendar (as a month will always span a minimum of 4 weeks / 4 rows), the first cell that requires this instruction is cell B8. We can use the following formula (remember that it must always reference the previous date cell, so cell B8 will reference cell H7, C8 will reference B8, etc.):

Date_Functions_10

When dragged across both of the bottom rows, this tells Excel to stop when it reaches the last day of April 2016. However, subsequent cells return errors because they are trying to reference a blank date cell.

This can be corrected by adding a simple check as to whether the previous cell is empty. If the cell is not empty, perform the EOMONTH() check, otherwise leave the cell blank.

Date_Functions_11

All of the formulas are now in place. The last thing that needs to be done is to format the header (cell B2) to display the name of the month as opposed to the date.

Note that cell B2 must always contain a date value, as the WEEKDAY() and EOMONTH() functions reference this cell as part of their calculations. However, we can format the cell to display the name of the month instead, using the ‘Number Formats’ dialogue again.

Select cell B2 and open the ‘Number Formats’ dialogue. Navigate to ‘Number’ and then to ‘Custom’. This time enter “mmmm” to display the date as the full month name.

Note that if you wish to include the year notation too, use the code “mmmm yyyy”.

Date_Functions_12

In my example, I have also changed the colour of the text in columns B and H as these are weekend dates, and I will not be assigning any events to them.

The monthly calendar is now set up. Typing a different date in cell B2 will dynamically adjust the calendar to display the dates for the chosen month and year (remember that the first date of the month must always be entered in cell B2).

Stay tuned for part 2 of this process in the next few weeks.

Download The Workbook
Date Functions (Workbook)

Get The Formula
The full formulas used in this blog are as follows:-

=IF(A4<>"",A4+1,IF(WEEKDAY($B$2)=COLUMNS($B$4:B4),$B$2,""))

Checks which weekday the first of the month falls on, and populates the appropriate cell with the first date of the month.

=IF(H7<>"",IF(EOMONTH($B$2,0)=H7,"",H7+1),"")

Checks whether the previous date cell contained the last date in the month, and continues advancing the date if not.

  • 24 Feb
  • Tobias Owen
  • Excel
  •  2 Comments
  •  Like
  • Calendar , EOMONTH() , Excel , Number formats , WEEKDAY()

Share This

Related Posts

  • Rock Paper Scissors
  • Calculate VAT in Excel
  • Rank by Multiple Criteria in Excel
  • Date Functions : Excel Calendar (Part 2)
Power BI – My Experience and Certification →← Supplier Performance Dashboard
1

Comment

  1. DATEDIF() : Excel’s Hidden Gem - Change This Limited
    6th July 2016 at 1:56 pm

    […] my Excel Calendar blog, I looked at how to use the DATE() function to calculate the first day of the month. I’ll use the […]

    Reply

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