Insert Drop Down Calendar in Excel Without Date Picker

Get FREE Advanced Excel Exercises with Solutions!

We need to use the calendar in Excel on a 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.


How to Insert Drop Down Calendar in Excel Without Date Picker: Step-by-Step Procedure

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.

Add Drop Down List of Months to insert drop down calendar in excel without date picker

Read More: How to Use Excel UserForm as Date Picker


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.

Add Drop Down List of Months to insert drop down calendar in excel without date picker

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

Data validation option to add drop down list

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

drop down menu list added by the data validation


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:

=TODAY()

Formulize Calendar Outline to insert drop down calendar in excel without date picker

  • Then select cell H4 and enter the following formula:

=MONTH(DATEVALUE(H5&1))

  • After clicking enter, we can see the month serial of the month mentioned in cell H4.

Month serial fix using the formula

  • Then enter 2023 in cell D5.
  • Then select cell B8 and enter the following formula:

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

  • After pressing Enter, you will see that first date appear at the cell B8.

Date of each month extracting

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

  • Then select cell C8 and enter the following formula:

=B8+1

  • After pressing Enter, you will see the date after the date mentioned in cell C8.

day count increased by one

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

date of one row is now fixed

  • Then again, select cell B9 and enter the following formula:

=B8+7

  • After pressing Enter, the date after the date mentioned in cell B9.

date of first column cell is now estimated

  • Then drag the Fill Handle to cell B13.

date of whole first column is now fixed

  • Now repeat the same process for the rest of the cells.
  • Finally, we got the dates for all of the weekdays in a month.

unformatted date in the calendar

Read More: Make an Alternative to Datepicker in Excel


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.

formatting the unformatted dates

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

Formatting cells

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

Condinational formatting using to insert drop down calendar in the workbook with date picker

  • In the Edit Formatting Rule window, select Use a Formula to Determine Which Cells to Format.
  • Then enter the following formula:

=MONTH(B8)<>$H$4

  • After entering the formula, click on the Format button.

Condition setting on the conditional formatting window

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

cell Format setting on conditional formatting

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

Final drop-down calendar without date picker

Read More: [Solved!] Datepicker Not Showing in Excel


Download Practice Workbook

Download this practice workbook below.


Conclusion

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.


Related Articles


<< Go Back to Excel Date Picker | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo