How to insert calendar in excel - javatpoint
A calendar is a visual element to pick a date from it. In Microsoft Excel, users can add to their sheet to represent the specific month. It provides an internal function to add a basic calendar layout and customize it.
Excel offers a variety of calendar templates. You can use any of them from it. It is a special feature of Excel that allows the users to add the calendar in an Excel sheet. Additionally, Excel enables various other methods to insert a calendar in an Excel file.
We will define all the possible ways to insert the calendar in an Excel sheet. Follow this chapter till the end to learn all the methods so that you can choose any one of them.
The calendar is a basic need in Excel. If the users want, they can include it in their sheets. It is beneficial for the users themselves when they are trying to plan the upcoming activities.
Sometimes, a user often needs to enter the date. If you manually enter the date one by one in the Excel cells, it takes too much time. Manual entries for dates require to be entered in a proper format. By adding the calendar to the Excel sheet will resolve this issue, and the user does not need to worry about the format.
After inserting the calendar, you just need to click the dropdown calendar and select a date from it. By using the calendar inside the spreadsheet, the user does not need to about the date format.
Users get some benefits when you insert the calendar to an Excel spreadsheet, such as -
There are several methods to insert the calendar into an Excel sheet.
Excel offers various calendar templates that you can use in an Excel sheet. These calendar templates need to be downloaded, which you want to insert in Excel. You can search and choose any one of them. Remember that you require an internet connection to search these templates.
You can insert the calendar into the Excel spreadsheet by following the simple process of inserting the calendar into Excel.
Step 1: Open an Excel sheet or create a new one. At the top left corner, click the File option.
Step 2: Next is to click the New button present on the left side panel of the Excel backscreen.
Step 3: On the Excel backscreen, you see a search box in which you type calendar and hit the Enter key to search the templates.
Step 4: Several calendar templates will appear there, such as Academic calendar, Seasonal calendar, etc. You can choose one from here to insert it into the current worksheet.
For example, We select the second template from here.
Step 5: Here, click the Create button to complete the insertion of the calendar to Excel sheet.
You see a left and a right arrow on the above screenshot. Using these arrows, you can change the template before creating it.
Step 6: A new workbook is created with your selected template as it is successfully inserted into the Excel sheet. This calendar has been inserted for January 2021.
This takes full screen of the Excel worksheet and till H15 cells. It was the only one-month calendar for the year 2021.
Step 7: If you want to change the month of the calendar, select the C1 cell to make it editable and enter the month name. Then, hit Enter to update the calendar for the modified month automatically.
Similarly, one can change the year by changing the targeted year in B1 cell.
Step 8: To change the year of the calendar, select the B1 cell to make it editable. Then, hit Enter to update the calendar for the inserted year automatically.
Another way to insert the calendar in an Excel sheet is Date and time picker control. Inserting a calendar in MS Excel is easy, but several users are not aware with the feature of Date and time picker control as it is hidden.
This feature is not easily available to the users as it is hidden initially in Excel. You can find this feature inside the Developer tab. Using this, one can insert the dropdown calendar to select a date to insert in a cell.
We will show you a complete example for this. Perform the following steps to insert a calendar using Date Picker control.
That's it.
Now, go through with each step as we explain.
Usually, the developer console is hidden with the installation of MS Excel. The users have to manually add it to the Excel menu bar if they need it to perform its related operations. Hence, the first step is to enable the developer console.
Perform the steps to enable the developer tab in MS Excel:
Step 1: Right-click anywhere on the Excel ribbon and choose to Customize the ribbon from it.
Step 2: A window of Excel Options will open where mark the Developer option checkbox available in the right pane and then click OK.
Step 3: You can now see that the Developer tab is added to the Excel ribbon between the View and Help tab.
A calendar in dropdown form is called as Date and Time Picker Control in Excel. It can be inserted into the Excel sheet easily. You need to follow a simple process. Go through with the following steps:
Step 1: In the Excel ribbon, navigate to the Developer tab where you see the control group.
Step 2: In the control group, click the Insert dropdown button and choose the More Control icon.
Step 3: A dialogue window will open for More Control, select Microsoft Date and Time Picker Control 6.0 (SP6) inside the ActiveX control, and then click OK.
At last, click on a cell wherever you want to insert it in the sheet.
Once the datepicker control is inserted, the EMBED formula will appear inside the formula bar. It indicates which type of control is embedded with the sheet.
The most common change that a user normally wants is: to resize the inserted calendar control and link with the particular cells so that it can be fully visible and easy to select date.
If the Microsoft Date and Time Picker Control 6.0 (SP6) is not available
In case this option is not found in the More Control list like in the below screenshot -
You need to follow the instruction below at the end of this chapter.
Once the calendar control is added to your sheet, you can now customize it. So, firstly move it to the desired location where you want to place it and get it fit in a cell.
Till now, you have successfully added a dropdown calendar control to your Excel sheet. Sometimes, you may also want it to link to a particular cell. It becomes mandatory for the users if they want to use the dates in formula for some other operations.
Scenario
Suppose that you want to count the difference between two dates. So, you have inserted the start date and end date using date picker control. When you try to calculate the number of days between the inserted date using COUNTIF(), it returns 0.
The reason for returning 0 is that Excel does not recognize the dates inserted by using the date picker control that is not associated with the cell till now. You have to link the date picker control to cells to fix this issue.
You will find a Properties option for the inserted calendar control near the Design Mode in the Developer tab. Click on this Properties option.
A window will open in which you see Linked Cell property. Enter the cell reference next to the Linked Cell whom you want to link and click OK.
Now, calculate the difference between the two inserted dates. It will now return the correct result.
Additionally, Excel users can also download the third-party tool or add-ins to insert the calendar in an Excel spreadsheet. It is the easiest way to insert a date and picker into Microsoft Excel.
You may find some tools paid and some are free. One can easily download them to MS Excel. If you also want to download the third-party tool, follow the steps below:
These were the different methods to insert the calendar in an Excel sheet. You can choose one which you prefer.
In earlier versions of Excel, Date and Picker control had come with the installation of Excel by default. But in Excel version 2016 and above, the users will have to download the Date and Picker control manually and register it in Excel. See how can do it:
Check if mscomct.ocx exists on your system
You can firstly check whether the Microsoft calendar control is available on your system or not. For this, you need to check mscomct.ocx file (date and time picker) in your system. To do this -
If you found this mscomct.ocx available in your system, you can skip the next step (download the date and time picker).
Download the date and time picker
If the date and time picker is not found on your system, you have to download it manually. You can download it by using the following link.
A zip file will download. Now, extract the downloaded file. You can extract the zip file by right-clicking and selecting the Extract files option like in the below screenshot.
Copy the extracted file folder to the C:\Windows\System64 folder.
The user must have administrative permission to copy the file to the system folder. But in case you still get the Destination Access Denied dialogue box, just click Continue on this.
Register calendar control on your system
The next step is to register the calendar control on your system after downloading and extracting the zip file. The calendar control must be downloaded as well as registered on the system.
Follow the steps to register the calendar control:
Search for the command prompt (CMD) in the start menu and run it as administrator.
In the command prompt, type one of the following commands according to your system configuration -
Our system is a 32-bit system. So, we will run the below command.
C:\Windows\System64\regsvr32.exe mscomct2.ocx
You will get the following message like below on successfully registering the mscomct2.ocx.
Now, restart the MS Excel once the mscomct2.ocx registered.
Then, navigate to the Developer tab that will now contain the Date and Time picker control and click the Insert and then More Controls.
You will see that - this time, Date and Time picker control there in the list.
Splunk
SPSS
Swagger
Transact-SQL
Tumblr
ReactJS
Regex
Reinforcement Learning
R Programming
RxJS
React Native
Python Design Patterns
Python Pillow
Python Turtle
Keras
Aptitude
Reasoning
Verbal Ability
Interview Questions
Company Questions
Artificial Intelligence
AWS
Selenium
Cloud Computing
Hadoop
ReactJS
Data Science
Angular 7
Blockchain
Git
Machine Learning
DevOps
DBMS
Data Structures
DAA
Operating System
Computer Network
Compiler Design
Computer Organization
Discrete Mathematics
Ethical Hacking
Computer Graphics
Software Engineering
Web Technology
Cyber Security
Automata
C Programming
C++
Java
.Net
Python
Programs
Control System
Data Mining
Data Warehouse
JavaTpoint offers too many high quality services. Mail us on [email protected], to get more information about given services.
JavaTpoint offers college campus training on Core Java, Advance Java, .Net, Android, Hadoop, PHP, Web Technology and Python. Please mail your requirement at [email protected]
Duration: 1 week to 2 week
Website Development
Android Development
Website Designing
Digital Marketing
Summer Training
Industrial Training
College Campus Training
Address: G-13, 2nd Floor, Sec-3
Noida, UP, 201301, India
Contact No: 0120-4256464, 9990449935
© Copyright 2011-2021 www.javatpoint.com. All rights reserved. Developed by JavaTpoint.
