Calendar Template in Excel

Excel Calendar Template

Excel has a wide variety of useful tools, but one of the criticism is it doesn’t have any built-in calendar tool. Yes, we don’t have any built-in calendar, I know you have seen some of the calendar templates in excel, but those are created by excel users. We need to create a calendar template in excel by using excel formulasUsing Excel FormulasThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more.

In this article, we will show you how to create a calendar template in excel. We can create calendar templates in two ways – one with a simple excel calendar and the second one a dynamic calendar with formulas.

calendar-template-in-excel.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Calendar Template in Excel (wallstreetmojo.com)

How to Create a Calendar Template in Excel?

You can download this Calendar Excel Template here – Calendar Excel Template

#1 – Simple Excel Calendar Template

A simple excel calendar template doesn’t require any special formulas; with simple techniques, we can create a simple calendar.

Step 1: The first thing we see with the calendar is Month with Year name. So for this, we need to create a Month Name. For this, first, apply the TODAY formula in excel, which takes the current date as per system.

calender template example 1.1

Today is a volatile function; it automatically updates the date as per the system date. Today’s date is 17th June 2019, so tomorrow it will be 18th June 2019.

However, we don’t need a full date like the above we need only Month & Year together. So apply the date format in excel as “MMMM YYYY.”

calender template example 1.2

Now we have a proper month & year.

Step 2: Now, in the below row, enter weekdays starting from Sunday to Saturday. And merge the A1 cell until the G1 cell.

calendar template example 1.3

Step 3: Now, we need to create calendar days like 1, 2, and 3, and so on.

calendar template example 1.4

I have entered the day with Sunday, 2nd Saturday, 4th Saturday, etc.

This is ordinary stuff that needs to change the dates every month manually. But how do we make this dynamic so that it shows the updated month with days? Let’s see this now.

#2 – Dynamic Excel Calendar Template with Formulas

For the same template as above, we will create a dynamic calendar.

Step 1: To the left of the template, insert Spin Button from the Developer tabDeveloper TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more.

calender template example 2.1
calender template example 2.2

Step 2: Right-click on the spin button and choose format control.

calender template example 2.3

Step 3: Under format control, make the below changes.

calender template example 2.4

Step 3: Since we are creating the calendar for the specific month, we will create 12 different sheets for 12 different months. For this, we need to create 12 different name managers. First, name the cell I3 as “Year.”

calender template example 2.5

First, we will create for January 2019. So change the heading of the month from the TODAY formula to as follows.

calendar template example 2.6

Now for this month, we need to create one more name manager, as shown below.

calendar template example 2.7

Similarly, by changing the month number, create a name manager for all the months.

Step 4: Now, with the same template above, insert 12 sheets and name each sheet with 12-month names.

calendar template example 2.8

Step 5: Now, in the “Jan” Sheet in the first-day column, apply the below formula.

=IF(AND(YEAR(JanOffset+1)=Year,MONTH(JanOffset+1)=1),JanOffset+1,””)

calendar template example 2.9

Copy and paste the formula to the next cell but change the number 1 to 2 everywhere.

calendar template example 2.10

Similarly, for the next change it to 3.

calendar template example 2.11

Similarly, increase everything by 1.

Step 6: Copy the above template to the FEB sheet. Here only thing we need to change is MONTH from 1 to 2.

calendar template example 2.12

Apply the same logic for all the worksheets. Change the month accordingly.

Once all the sheets are done with template changing and formula, we can control the template from the “Jan” Sheet.

Step 7: Using the “Spin” button, we can change the Year in the “Jan” sheet. Accordingly, when we change the year in the “Jan” sheet in all the sheets, the selected year’s dates will show up.

calendar template example 2.13

Recommended Articles

This has been a guide to the Calendar Template in Excel. Here we discuss how to create a simple and dynamic calendar template in excel using formulas with a downloadable excel sheet. You can learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>