We need to use the calendar in Excel on daily basis. But for every reason, using a date picker calculator can be quite a hassle. This type of customized calendar is quite handy. In the below article, we will show how you can insert a drop-down calendar in Excel without the date picker.
Download Practice Workbook
Download this practice workbook below.
Step-by-Step Procedure to Insert Drop Down Calendar in Excel Without Date Picker
In a detailed step-by-step procedure, we showed below how you can insert the calendar without the date picker in Excel. For better functionality, try to use the Excel 365 version for this method.
Step 1: Prepare Calendar Layout
Before we delve into creating the schedule, it is imperative that we create the outline of our calendar first, in which we are going to implement the formulas.
- Firstly, we need to prepare the layout of the outline of the calendar.
- To make it dynamic, we need to place the date and month on the sheet.
- The date and the month should be dynamic to today’s date.
- Our calendar will follow the weekdays starting from the Monday format.
- We also have all the month’s names in the range of cell J4:K17. This name with serial will be helpful in the next steps.
Read More: How to Create a Date Picker in Excel Using VBA (with Easy Steps)
Step 2: Insert Drop Down List of Months
After we finalized our outline, we can add a drop-down list of months, which will make our month selection much easier.
- In order to add a drop-down list, select cell H5.
- Then go to Data > Data Tools > Data Validation > Data Validation.
- In the Data Validation window, go to the Settings tab.
- Select List in the drop down to allow list.
- And select the range of cells J5:J16 in the source range box.
- Press OK after this.
- Now we can see the month names in cell H5 in the form of a drop-down list.
Step 3: Formulize Calendar Outline
After we get the outline, it is time to formulate the calendar to make it dynamic. The functions like TODAY, MONTH, DATE, DATEVALUE, and WEEKDAY to achieve this.
- As we have the structure of the calendar plotted in the worksheet, we formulate the calendar to make it more dynamic.
- Next to extract today’s date in the dataset, select cell D4, and enter the following formula:
- Then select cell H4 and enter the following formula:
- After clicking enter, we can see the month serial of the month mentioned in cell H4.
- Then enter 2023 in cell D5.
- Then select cell B8 and enter the following formula:
- After pressing Enter, you will see that first date appear at the cell B8.
🔍 Formula Breakdown
⮚ This will return the date in the proper date format. The month is from cell I4, and the year mentioned is 2022. And the date is 1.
⮚ This will subtract the weekday from the date value. It will make sure that Monday stays at the front of the calendar.
- Then select cell C8 and enter the following formula:
- After pressing Enter, you will see the date after the date mentioned in cell C8.
- Then drag the fill handle to cell H8 from cell B8.
- Doing this will fill the range of cells with dates starting from the 29th of October to the 4th of September.
- Then again, select cell B9 and enter the following formula:
- After pressing Enter, the date after the date mentioned in cell B9.
- Then drag the Fill Handle to cell B13.
- Now repeat the same process for the rest of the cells.
- Finally, we got the dates for all of the weekdays in a month.
Read More: Make an Alternative to Datepicker in Excel (2 Easy Methods)
Step 4: Reformat Data Values
As we have the date values of the calendar, they are not in a good format. we need to re-format them into c correct conventional calendar format.
- Then right-click on the mouse, and from the context menu, click on Format Cells.
- In the Format Cells window, go to the Number tab.
- Then in the Custom options, select the Type field.
- In the Type field, enter “dd” only.
- Hence, this will allow the user to see only the day portion of the date.
- Click OK after this.
- After that, our calendar will now have the dates in a double-digit format.
- Moreover, we have the dates from the previous and next month.
- But we want to avoid that.
- To avoid that, we need to conditionally format the values.
- For this, select the whole calendar and then go to the Home tab > Conditional Formatting > New Rule.
- In the Edit Formatting Rule window, select Use a Formula to Determine Which Cells to Format.
- Then enter the following formula:
- After entering the formula, click on the Format button.
- In the Format Cells dialog box, click on the Fill tab.
- Then in the Fill tab, choose the color white as the fill color.
- Then switch to the Font tab and choose the Color white as the font color.
- Click OK after this.
- Hence, the dates from the other months are now omitted from the view.
- Finally, now as we enter our desired year and month from the drop-down list, we get the calendar showing exact same weekdays of months and year.
Read More: [Solved!] Datepicker Not Showing in Excel
To sum it up, we showed in this article how we can insert the drop-down calendar in Excel without a date picker. For this problem, a macro-enabled workbook is available to download where you can practice these methods. Feel free to ask any questions or provide feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.