Insert Drop Down Calendar in Excel Without Date Picker

Step 1 – Prepare Calendar Layout

• Prepare the layout of the outline of the calendar.
• Place the date and month on the sheet.
• The date and the month should be dynamic to current date.
• The calendar will follow the weekdays starting from the Monday
• All the month’s names are in the range of cell J4:J17.

Step 2 – Insert Drop Down List of Months

• To add a drop-down list, select cell H5.
• Go to Data > Data Tools > Data Validation > Data Validation.

• In the Data Validation window, go to the Settings
• Select List in the drop down to allow
• Select the range of cells J5:J16 in the source range box.
• Press OK.

• We can see the month names in cell H5 in the form of a drop-down list.

Step 3: Formulize Calendar Outline

• To extract the current date in the dataset, select cell D4, and enter the following formula:

`=TODAY()`

• Select cell H4 and enter the following formula:

`=MONTH(DATEVALUE(H5&1))`

• Press Enter to see the month’s serial in cell H4.

• Enter 2023 in cell D5.
• Select cell B8 and enter the following formula:

`=DATE(\$D\$5,\$H\$4,1)-WEEKDAY(DATE(\$D\$5,\$H\$4,1),2)+1`

• Press Enter to see that first date appear in cell B8.

Formula Breakdown

• DATE(\$D\$5,\$H\$4,1)

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

• =DATE(\$D\$5,\$H\$4,1)-WEEKDAY(DATE(\$D\$5,\$H\$4,1),2)+1

⮚ This will subtract the weekday from the date value. It will make sure that Monday stays at the front of the calendar.

• Select cell C8 and enter the following formula:

`=B8+1`

• Press Enter to see the result in cell C8.

• Drag the fill handle from cell B8 to cell H8
• This will fill the range of cells with dates starting from the 29th of October to the 4th of September.

• Select cell B9 and enter the following formula:

`=B8+7`

• Press Enter to see the result in cell B9.

• Drag the Fill Handle to cell B13.

• Repeat the same process for the rest of the cells.
• The result shows the dates for all of the weekdays in a month.

Step 4: Reformat Data Values

• Right-click and choose Format Cells.

• In the Format Cells window, go to the Number
• In the Custom options, select the Type
• In the Type field, enter “dd”
• This will allow the user to see only the day portion of the date.
• Click OK.

• The calendar will now have the dates in a double-digit format.
• We have the dates from the previous and next month.
• To avoid that, we need to conditionally format the values.
• Select the whole calendar and 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.
• Enter the following formula:

`=MONTH(B8)<>\$H\$4`

• Click on the Format button.

• In the Format Cells dialog box, click on the Fill
• In the Fill tab, choose the color white as the fill color.
• Switch to the Font tab and choose the Color white as the font color.
• Click OK.

• The dates from the other months are now omitted from the view.
• Enter the desired year and month from the drop-down list to get the calendar to display the exact same weekdays of months and year.

Related Articles

<< Go Back to Excel Date Picker | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF