• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

Calendar Template in Excel

Home » Excel » Excel Charts » Calendar Template in Excel

By Tejswini Bhosale Leave a Comment

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

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.

How to Create a Calendar Template in Excel?

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

#1 – Simple Excel Calendar Template

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 TODAY formula 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, to 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 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.

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

calendar template example 1.4

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

This is ordinary stuff which 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 Developer tab.

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 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 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 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 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 “Jan” Sheet.

Step 7: Using the “Spin” button we can change the Year in “Jan” sheet, accordingly when we change the year in “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 Calendar Template in Excel. Here we discuss how to create simple and dynamic calendar template in excel using formulas with downloadable excel sheet. You can learn more about excel from the following articles –

  • Make Blank Invoice Excel Template
  • 4 Ways to Reduce Excel File Size
  • How to Create Animation Chart?
  • How to Evaluate Formula in Excel?
  • Create Marimekko Chart in Excel
  • Create Personal Budget Template in Excel
  • How to Create Templates in Excel
  • Create Family Tree in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Excel Charts

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download Calendar Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

CYBER WEEK OFFER - All in One Excel VBA Bundle (35 Courses with Projects) View More