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.

#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.

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.

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.

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

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.

#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.

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

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

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.

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

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

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.

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,””)

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

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

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.

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.

• 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.