Monthly Calendar
Dec 23

How to Create a Printable 2018 Monthly Calendar Template

2018 is Coming!

Dear readers, the new year is just around the corner! Recently I had a colleague coming to me, and asked me if I can create a monthly calendar template for him which will generate automatically when the year and month are selected so that he can plan his next year’s appointments.

So after getting some inspiration from this Microsoft’s template, here’s my version of a dynamic printable monthly calendar template for 2018 (or any year in fact!):

Monthly Calendar in action

 

Are You in for this Project?

If you are just looking to download this printable monthly calendar, here’s the download link.

However, if you are up for the challenge, let’s get our hands dirty by creating this template from scratch. Through this challenge, you will learn and practise some of these Excel features:

  • creating a dropdown list
  • custom number formatting
  • defined names
  • VLOOKUP
  • working with arrays
  • INDEX
  • conditional formatting
  • setting print area

A word of caution: I would rate this project as 4/5 in difficulty. It is challenging because most of you are not familiar with all of the above Excel features which I utilized. But don’t worry, I will be with you every step of this project.

Once you finished this project, I’m sure you’ve learnt something new, and also have something useful for you to use and amaze your friends and colleagues with!

Ready? Let’s open up Excel and starting at it right away!

 

Designing the Calendar Template

Template Design

  1. First, I made columns B to H wider (I used a column width of 20).
  2. Next I made rows 4, 6, 8, 10, 12, 14 longer (I used a row height of 60). This is the area for you to enter your appointments, deadlines etc.
  3. Then I use outside borders to draw the boxes. Each box consists of a small cell that will display the date later and a large cell for writing.
  4. Lastly, I turned off the gridlines (View | Show | Gridlines) to make it look elegant.

 

Creating a Dropdown List for Month Selection

Dropdown List for Month

If you are creating an Excel sheet for others to use, creating a dropdown list for users to select is the best way to prevent them from keying in something else.

  1. Select cell B1 and go to Data | Data Tools | Data Validation.Data Validation
  2. Under Allow, choose List. This will create a dropdown selection list.
  3. Under Source, key the following into the box:
    JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
  4. For cell C1, key in the year that you want for your calendar.

 

Applying Makeup to Hide the Numbers

In cells B2 to H2, we are going to write the days of the week. However, later in the calendar dates, we will somehow need to align the dates to their corresponding days of the week. Excel can’t read the days of the week from the text Mon, Tue, Wed, etc.

So we need to have the cells B2 to H2 holding numbers in the background for Excel to do its calculation, while displaying Mon, Tue, Wed, etc. for us. How do we do that?

Enters Number Formatting.

Number Formatting allows us to apply a makeup on top of what is actually in a cell. You may not be aware of this “makeup” effect, but it is actually very commonly used. For example, 0.8 being displayed as 80% or 1234.5 being displayed as $1,234.50.

Number formatting

  1. To do this, first enter the numbers 1 to 7 into cells B2 to H2. Setting up Days of Week 1
  2. Next select cell B2, and use the keyboard shortcut Ctrl+1. In the Formal Cells window, select Custom from the Category, and under Type enter:
    "MON"
    Note: I’m creating a monthly calendar template starting Monday. If you want to create yours starting Sunday, use “SUN” instead of “MON”.Setting up Days of Week-2
  3. Repeat this 6 more times for the rest of the week.

We have completed the design of the calendar template. Check if yours look similar to the screenshot below.

Completed Template Design

 

Using VLOOKUP to Convert Month Name to Month Number

Now we will start with the heavy work behind the scenes.

First let’s tackle the issue of how we can convert the month name (e.g. NOV) to the month number (11) so that we can use it in our calculations later.

We will use VLOOKUP to lookup the cell B1 which contains the month name, to a reference table and return its corresponding month number. (*I’m not explaining how VLOOKUP works in this article, but if you are not familiar with it, do a Google search or comment below.)

However, because we do not want to display the result (the month number) anywhere on the sheet, we can hide the result in a Defined Name.

Defined Names are like storage boxes that you can put stuff in, label them, and in the future retrieve the boxes easily when you need.

Defined Names

Let’s first try a simple one — storing the value in cell C1 into a box labeled as Year.

Go to Formulas | Defined Names | Define Name. Let’s name this box as Year and under Refers to (what we want to put into this box), use your mouse and click on cell C1. Defined Name - Year

And now, for the Month Number:

  1. Define a new name and call it Month.
  2. Under Refers to, type in the following:=
    VLOOKUP($B$1,{"JAN",1;"FEB",2;"MAR",3;"APR",4;"MAY",5;"JUN",6;"JUL",7;"AUG",8;"SEP",9;"OCT",10;"NOV",11;"DEC",12},2,0)
    *Note: be careful of the comma , and semi-colon ;
  3. What we just did was to VLOOKUP cell B1 in a reference table and return the corresponding month number. The whole table is keyed into the formula with “,” being the next column, and “;” being the next row.

More Defined Names

Then we will create another defined name called WeekOption. In Refers to, use 1 if your calendar starts on a Sunday and 2 if it starts on a Monday. We will use this defined name later for Excel to return a numerical value for which day of the week a date falls on.

Defined Name - WeekOption

Next we need to create a matrix with 6 rows (there are a maximum of 6 weeks for any month) and 7 columns (for each day of the week), and to number them 1, 2, 3, and so forth. The reason why we need this is because once we have the first date in the first cell, the next cell is one day after it and so forth.

Calendar Matrix

Creating a matrix like this is not difficult, but once again like the VLOOKUP we did earlier, we need to store this entire matrix into a defined name since we don’t want this to be displayed on the sheet.

So we create a new defined name Matrix, and under Refers to, we enter the following:

={1,2,3,4,5,6,7} + 7*{0;1;2;3;4;5}

This replicates the matrix and stores it into the box.

 

Gathering the Ingredients

To recall, we’ve prepared these ingredients in our earlier steps:

  • Year (the year of the calendar)
  • Month (the month number of the calendar)
  • WeekOption (having the value of 1 or 2, depending on which day of the week to start the calendar)
  • Matrix (a 6 x 7 table of sequential numbers)

 

The WEEKDAY() function

Just in case you are not familiar with the WEEKDAY() function, it simply returns a number for which day of the week it falls on.

The WEEKDAY() function requires two inputs — a date (of course) and the return_type. We usually use the following return_type:

1 — if the week starts on a Sunday. WEEKDAY() then returns 1 for Sunday, 2 for Monday and so on..

2 — if the week starts on a Monday. WEEKDAY() then returns 1 for Monday, 2 for Tuesday and so on..

E.g. The date 1st January 2018 is a Monday, and if we use return_type 1, we will get a value of 2. If return_type 2 is used instead, we will get the value of 1.

*Note that the return_type we choose here, must be the same as how you setup your days of the week in cells B2 to B5.

 

Finding the First Date of Your Calendar

Here’s where the entire magic is.

With everything prepped now, we want to find the first date that should be on our calendar (I’m using Monday as the starting day of the week). This date is usually coming from the previous month, unless the 1st of the month happens to be a Monday.

Consider this: The 1st of November 2017 is a Wednesday (or the numerical value of 3 using the WEEKDAY function). Then the first day on my calendar needs to be 2 days before the 1st November 2017.

Thus: first_day_of_the_month – WEEKDAY() + 1 is the first day on my calendar.

And what’s the second day on my calendar? It’s first_day_of_the_month – WEEKDAY() + 2.

Do you recall where are these 1 and 2 coming from? *hint: it’s in one of our storage boxes.

Right! We already have that from our matrix of numbers from 1 to 42. Hence the calendar dates are calculated by: Matrix + first_day_of_the_month – WEEKDAY().

Now let’s create a new defined name for Calendar. Under Refers to, enter the following:

= Matrix + DATE(Year, Month, 1) - WEEKDAY(DATE(Year, Month, 1), WeekOption)
*Notice how we retrieve the boxes by simply using their label names.

 

The INDEX() Function – Getting what we want

Phew~ Now that the toughest obstacle has passed, we can cruise through the rest of this project.

Time to finally add the dates to our monthly calendar.

To recap what we have just did in the previous step, the defined name Calendar is now a 6 x 7 matrix listing all the relevant dates. We will call out parts of Calendar that we want to display using the INDEX() function.

The INDEX() function is like referring to a map and trying to call out a location using the longitude and latitude coordinates. We provide a map for Excel to refer to, state which row and column to look, and Excel will tell you what’s there with your given Excel “GPS” co-ordinates.

A typical INDEX() function will look like:

= INDEX(Calendar, row_number, column_number)

where the row_number is 1 to 6, depending on which week of the month it is, and the column_number is 1 to 7, corresponding to the underlying numbers of cells B2 to H2 underneath the “makeup”.

But we will may experience an issue if we do this:

Users may accidentally overwrite the cell containing the formula when they are entering their appointments into the calendar.

 

Trick to Prevent Changes to a Cell

We want to prevent changes to a cell, i.e. no deleting, overriding cell contents.

How can we do that?

Have you tried breaking a single wooden chopsticks or toothpick? It’s pretty easy right?

But if you need to break a bunch of chopsticks like the picture above, it’s so much harder.

The key is being united.

We will utilize the same concept of making the formula an array formula (meaning a formula for a group of cells) rather than a normal formula for a single cell.

Chopsticks

If the user tries to modify part of the array, they’ll see this error message:

Array error

 

Putting Dates into the Calendar

Alright, so we need to use the INDEX function to pull out parts of the defined named Calendar, and somehow enter the INDEX function as an array formula so that users will not be able to delete or edit any single cell containing the date.

Here it goes:

  1. Select cells B3 to H3.
  2. In the formula bar, type in the following formula:
    =INDEX(Calendar,1,B$2:H$2)
  3. Press Ctrl + Shift + Enter instead of Enter to register it as an array formula.
  4. Excel will insert the curly braces in the formula bar to show you that it understands it as an array formula.
  5. Repeat it for Weeks 2 to 6 of your calendar, using the following formulas:
    =INDEX(Calendar,2,B$2:H$2)
    =INDEX(Calendar,3,B$2:H$2)
    =INDEX(Calendar,4,B$2:H$2)
    =INDEX(Calendar,5,B$2:H$2)
    =INDEX(Calendar,6,B$2:H$2)

At this point, you should see something like this. Don’t freak out, the dates are there.

Remember our point that number formatting is just a makeup? The usual date format that we see is just applying makeup to the serial date numbers.

Monthly Calendar in General Format

The Final Magical Touches

For the final touches, we will first apply makeup to the serial date numbers to just display the day of the month.

To do so:

  1. Select all the dates, 42 of them. (*hint: you can hold down the Ctrl key for multiple selections.)
  2. Go to Home | Number, click the dropdown menu, go to More Number Format. (hint*: or just simple use the keyboard shortcut Ctrl + F1.)
  3. In the Format Cells window, go to Number | Category | Custom.
  4. Inside Type, key d for day of the month. Press OK.

Number Formatting is a Makeup

The final (optional) thing to do is whether you want to hide the dates of the previous and next months, or to show them in a lighter color.

To do a custom Conditional Formatting:

  1. Select all the 42 dates again, making sure to start from cell B3.
  2. Go to Home | Styles | Conditional Formatting | New Rule.
  3. In the New Formatting Rule window, click on Use a formula to determine which cells to format.
  4. In the field Format values where this formula is true, type in the following formula:
    =MONTH(B3)<>Month
  5. Under Edit the Rule Description | Format, in the Format Cells window, go to Font | Color. Choose a light grey (or white if you want to hide the dates of previous and next months).

Set Printing to A Single Page

I have previously covered how to print to a single page easily on Top 10 Excel tips for Excel Beginners (Tip #9).

Here, we want to specify the area for printing. To do that:

  1. Select cells B1 to H14.
  2. Go to Page Layout | Page Setup | Print Area | Set Print Area to specify these cells to be printed.
  3. Then choose Page Layout | Page Setup | Orientation } Landscape.
  4. Finally, select Page Layout | Scale to Fit. Set Width and Height to 1 page.

The Final Product

Yes, we have finally reached the end of this project to build a Month Calendar template to generate automatically for any year month!

Here’s the finally calendar.

Monthly Calendar
Let me know in the comments below if you have any problems getting to the final product!

And wishing you a blessed festival holidays!

 

P/S: Special thanks to Martin Loughman from Spreadsheeti.com for highlighting that the maximum number of weeks in a month is 6!

Dealing with duplicates
Jun 15

How to Find and Remove Duplicates in Excel

No matter which profession you are in, one annoying enemy is always present — duplicates. They multiply themselves like clones, and no matter how many times you scroll and press Delete, they always seem to be never-ending.

Arrggh!

Today we’ll discuss simple and fast methods to find and remove these same values from our Excel worksheets.

Exterminate these pesky identicals, you will!

Here’s how…

Continue reading

VLOOKUP vs INDEX-MATCH
May 16

VLOOKUP vs INDEX-MATCH: 27 Excel experts share their opinions

VLOOKUP vs INDEX-MATCH. Which is better?

This has got to be one of the oldest, and yet hottest topic among the Microsoft Excel's community.

Currently there are about 1.43 million search results in Google just on this topic alone.

And so, DefeatExcel has reached out to seek the opinions of Excel experts such as Microsoft MVPs and Excel community experts so that we can hear their views right here in a single article. 

VLOOKUP vs INDEX-MATCH
Continue reading
Excel for HR Professionals - 7 Essential Functions
May 06

Excel for HR Professionals: 7 Essential Functions That Will Cut Your Work From Hours to Minutes

Recruitment, training & development, compensation & benefits, payroll, employee relations, staff retention, ... 

HR professionals are often seemed to have the easiest jobs in the company. 

Or is it?​

In reality, what goes behind the scenes are the endless amount of human resource information system (H.R.I.S.) inputs, preparing reports for the management, and spending face-time with employees just to get the day-to-day work done.

Here's some Excel magic to help you cut your day-to-day HR work from days and hours to just MINUTES.

Excel for HR Professionals - 7 Essential Functions
Continue reading
Mar 01

Creating an Excel Gantt Chart

Creating an Excel Gantt chart. #free #Gantt #Excel #MSExcel | DefeatExcel.com

Recently at work, we just started on a new project and wanted to map out all the tasks in a Gantt chart format. For those of you who are not familiar with Gantt charts, a Gantt chart simply illustrates a project schedule in a visual form, making it easier for users of the Gantt chart to follow the task summary.

Continue reading