Excel Calendar Template

Article byJeevan A Y
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Excel Calendar Template?

Caelndar Template act as a planner and help us create schedules. Though Excel has a wide variety of useful tools and features, it does not have an in-built calendar template. However, we can create a calendar template in Excel using Excel formulas.

This article will show you how to create a calendar template in Excel. We can generate calendar templates in two ways – one with a simple Excel calendar and the second with 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: <strong>Excel Calendar Template</strong> (wallstreetmojo.com)

Key Takeaways

  • Calendar templates in excel are not in-built by default.
  • We can create them in excel using excel formulas.
  • There are 2 types of calendar templates in excel. They are simple Excel calendar and dynamic calendar.
  • To create simple excel calendar, we can use TODAY excel function.
  • The TODAY function displays the current date in proper sequence.
  • The Spin button is available in the developer tab. By default, developer tab is not enabled. So, we have to enable it manually.

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 does not require special formulas with simple techniques. We can create a simple calendar.

Step 1: The first thing we see on the calendar is the month with the year’s name. Therefore, we need to create a month’s name.

First, apply the TODAY formula in Excel, which takes the current date as per the system.

calender template example 1.1

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

However, we do not need a full date like the above; we need only a month and a year together. So, apply the date format in Excel as MMMM YYYY.

calender template example 1.2

Now we have a proper month and year.

Step 2: Next, in the below row, we will enter weekdays from Sunday to Saturday. Then, we should merge the A1 cell until the G1 cell.

calendar template example 1.3

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

calendar template example 1.4

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

Clearly, we need to change the dates every month manually. But how do we make this dynamic to show the updated month with days? Let us see this now.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#2 – Dynamic Excel Calendar Template with Formulas

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

Step 1: First, to the left of the template, we will insert the Spin Button from the Developer tab.

calender template example 2.1
calender template example 2.2

Step 2: Next, right-click the spin button and then, choose Format Control.

calender template example 2.3

Step 3: Now, under Format Control, we will make the below changes.

calender template example 2.4

Step 4: Since we create the calendar for a specific month, we will create 12 different sheets for 12 months. For this, we need to create 12 different name managers.

Therefore, we will name cell I3 Year.

calender template example 2.5

Now, first, we will prepare for January 2019. So, we will change the month’s heading from the TODAY formula to follows.

calendar template example 2.6

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

calendar template example 2.7

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

Step 5: Next, we will insert 12 sheets and name each sheet with 12-month names with the same template above.

calendar template example 2.8

Step 6: Then, we will apply the formula below in the Jan sheet in the first-day column.

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

calendar template example 2.9

Now, we may copy and then, paste the formula to the next cell but change the number 1 to 2 everywhere.

calendar template example 2.10

Similarly, for the next, we will change it to 3.

calendar template example 2.11

Similarly, increase everything by 1.

Step 7: Next, we will copy the above template to the FEB sheet. Here, the only thing we need to change is MONTH from 1 to 2.

calendar template example 2.12

Then, we will apply the same logic to all the worksheets. Now, we will 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 8: 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

Important Things To Note

  • Templates in Excel provide layouts for us to use and insert values.
  • In Excel, there are many pre-designed templates such as accounting and calendar templates in excel.
  • Calendar Templates in Excel is useful to create a simple layout to prepare timetables, and schedules.

Frequently Asked Questions

1. What is Calendar Template in Excel?

Calendar templates in Excel are one of the types of Excel templates which are not available by default. But instead, we can create calendar templates in Excel using Excel formulas.

2. Why should we use the TODAY function in Excel?

To create a simple calendar template, we use the TODAY function in Excel. The TODAY Excel function is useful when we have to display the current date in the worksheet.

The syntax of the TODAY function is =TODAY()

3. What are the uses of calendar templates in Excel?

Calendar templates in Excel are highly helpful as we can create schedules and timetables, and plan our work. Calendar templates save a lot of time and also provide default values that we can reuse.

Recommended Articles

This article is a guide to Calendar Template in Excel. We discuss how to create a calendar in Excel using formulas with a downloadable sheet with a downloadable Excel sheet. You can learn more about Excel from the following articles: –