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.
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.
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.
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.
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:
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”.
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:
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.
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:
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.):
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.
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”.
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.
Eoin Sharkey
Thanks for posting the Tax Year formula.
Simple but effective. My brain was too tired to work this out, and copying and pasting this allowed me to classify 5 years worth of Child Benefit statements into correct tax year in 30 seconds so …Yay !