Create and Insert Calendar in Excel
Inserting Calendar is a useful feature provided by Excel. It will become more useful when you are working on a shared worksheet where you will have to enter date very often. This will help you to reduce your time in doing manual entries, remember about the format, etc.
Inserting a calendar in excel will help you to resolve these problems. You just need to insert a drop-down calendar and select the date from there.
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: Insert Calendar in Excel (wallstreetmojo.com)
Steps to Insert & Create Calendar in Excel
Following are the ways to insert and create a calendar in Excel:
#1 – Inserting Calendar based on Templates available in Excel
There are a number of Excel Calendar templatesExcel Calendar TemplatesCalendar Template in Excel offers a pre-designed structure of a calendar that can be customized according to one's needs. Excel calendar can be used for school curriculums, desk calendars etc.read more available in Excel. You can choose any one of them according to the requirement by following the below steps.
Click on Files Select New Search for Calendar in the search box. This will show you a number of Excel calendar templates by categories, i.e., Seasonal Photo Calendar, Academic Calendar, Any Year Calendar, etc. See the screenshot below to know how to insert free excel calendar templates.
#2 – Inserting Seasonal Photo Calendar in Excel
Click on File and Select New search “Seasonal Photo Calendar” from the search box
Select the Calendar and Click on Create.
It will look like this.
Enter Year below Year Cell, and it will automatically update the calendar for that year.
All the 12 sheets with month name will be updated according to the year entered in January Sheet. Week start date can be select as Sunday or Monday (This will update the first cell of the calendar as Sunday or Monday).
Note: Enter formula =YEAR(TODAY()) in a cell below the Year cell. This will show you the current year Calendar whenever you will open it.
You can follow the above steps and search calendar from Excel online template according to your requirement and start working on it.
Want to know more about these functions –
- Year Function in ExcelYear Function In ExcelThe year function in excel is a date function to calculate the year from a given date. This function takes a serial number as an argument and returns a four-digit numeric value representing the year of the given date, formula = year (serial number)read more
- Today Function in excelToday Function In ExcelToday function is a date and time function that is used to find out the current system date and time in excel. This function does not take any arguments and auto-updates anytime the worksheet is reopened. This function just reflects the current system date, not the time.read more
#3 – Using Date Picker Control
Date Picker Control is ActiveX Control, which can be found under the Excel Developer TabExcel Developer 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. This is normally hidden and can’t be seen directly under ActiveX controls. You need to explore the ActiveX Controls.
Below are the steps to enter Date Picker control:
First, check if the Developer tab is there in Menu Bar. If you can’t see the Developer tab, then follow the below-given steps.
- Go to File Menu and click Options.
- Select a customized ribbon in excel and check the Developer option.
You can see the Developer Tab in the Menu bar. - Click on insert under the Developer tab (It will look like the setting button).
- A dialogue box (More Control) will open as below. Search for Microsoft date and Time Picker control 6.0 (SP6) and select it. Click Ok.
- When you select Microsoft date and Time Picker control 6.0 (SP6), it will enter a drop-down calendar.
- You can select the desired date, whatever you would like to select. For that, first, you have to turn off the Design Mode.
You can move your calendar to the desired cell. Right-click on the date picker and move it to the desired cell.
Example of Date and Time Picker
Below is the example of using Date and Time Picker.
Suppose you need to get the difference between two dates. Enter one date picker in Cell A2 and another in cell C2.
Excel cannot recognize the value of a date picker control. Link your date picker controls to certain cells in the following way to fix this:
Select first date picker, and under the developer, tab clicks on properties.
In Properties, at LinkedCell type A2. This will Link your date picker control to A2 cell.
Similarly, link the second date picker to the cell C2.
Now enter the formula =C2-A2 in Cell E2. Select date from both date picker.
We have selected 1/1/2019 in Date picker 1 and 2/23/2019 in Date picker 2. Now we enter formula =C2-A2 in Cell E2.
This will calculate the difference between the selected dates.
Calculate the days between both the date and the result is 53.
Here we don’t need to worry about the format and don’t care about manual date writing. we just need to click on the dropdown list in excelDropdown List In ExcelA drop-down list in excel is a pre-defined list of inputs that allows users to select an option.read more, and this will show Calendar. We just need to select a date from there.
How to Install Third-Party Add-ins for Excel Calendar?
Go to google, search for the given third-party add-ins, then download and install it.
After Installation, you can see this third party add-in in excel Menu Bar.
The above image is a screenshot of Ablebit’s tools.
Under Ablebits Tools, Click on Date picker, and this will activate Date Picker.
Now enter any date in desired cells. A date picker pop-up will show at the top right corner of that cells.
Click on the pop-up, select the desired date, and start working.
Ablebits Date Picker Tools Additional Options
Use ^ to go to the previous month and another button to go to next month. Click August 2018 to select another month or year directly instead of moving month one by one.
Things to Remember
- Microsoft Date and Time picker are available on 32-bit Windows only.
- If you are using a 64-bit version, then you will not be able to use Date and Time Picker.
- For a 64-bit version, you need to use Third Party External Add-ins Tools for your ExcelAdd-ins Tools For Your ExcelAn add-in is an extension that adds more features and options to the existing Microsoft Excel.read more.
- Some of the third-party Add-ins available in the market are Excel Date PickerExcel Date PickerExcel Date Picker is the drop-down calendar that facilitates the user to put the dates in the excel worksheet promptly. It is inserted with the help of ActiveX Control and is not available for the 64-bit version of MS Excel.read more, Ablebits Date Picker, Popup Calendar add-in, etc.
- Installing third-party add-ins is very easy and user-friendly. Just download and install it, and you are all set to use the tool.
Recommended Articles
This has been a guide to Excel Insert Calendar. Here we discuss how to insert and create a calendar in excel using various methods like date picker controller and third-party add-ins, etc. You can learn more about excel functions from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
58.6K subscribers
Leave a Reply