UPDATE on 7/24/20202: Don’t miss the video at the end of this post.
Did you ever ask why Excel does not provide Dropdown Calendar for date selection? Like the one shown below:
I did. I asked that because I didn’t know its existence in Excel as it is so well hidden. So I would like to share with you, just in case you need it. First, you need to go to the Developer Tab. In case it is not on your ribbon, follow the following steps 
- Go to File Tab (or Ribbon icon for Excel 2007)
- Go to Customize Ribbon
- Check the “Develop” on the right under Main Tabs
- OK
- There you go
- Now go to Developer tab
- Select “Insert”
- Click on “More Controls” – the lowest right icon
The “More Controls” dialog box is opened Scroll down and look for “Microsoft Date and Time Picker Control 6.0 (SP6)”
Now you should see the DTPicker (Dropdown box) 
Pls note that the “Design Mode” is turned ON, meaning you can edit (move/resize/change properties) the DTPicker. Don’t worry about the formula shown. Properties Setting – Most of the time, you want to Link the value in the DTPicker to a specific cell.
To do this:
- Right Click the DTPicker (Make sure the “Design Mode” is ON)
- Select Properties
- In the Properties Windows, input the Cell Reference you want in the Linked Cell. Let’s input A1 for this example
- You may see an error message box. Just ignore it
- Now you may want to resize the DTPicker by drag and drop a corner of it
When you have finished property setting, resizing and moving it to your designated location, you need to turn OFF the “Design Mode” in order to activate the DTPicker To experiment it, click on the dropdown arrow to display the calendar below:
- Select the date you want
- The date selected is LINKED to the cell you have assigned, i.e. A1 in this case
That is it. Make every day count. Reminders:
- If you want to edit or even move the DTPicker, you have to TURN ON the “Design Mode” first
- The date on the Linked Cell is actually set as Text not Date. Normally it won’t affect date-related calculation but you may not apply Date format on that cell directly.
UPDATE:
Mr. Excel shares a great tip in having a date picker in all versions of Excel! Check it out!
Thanks Mr. Excel for sharing the tip; and Thanks SAM RADAKOVITZ for the creation! Super!
Want more date-related topics? Please read:
- What we need to know about inputting Date in Excel?
- Advantages of having date input correctly in Excel
- Fixing trouble dates
- Convert an 8-digit number into Excel-recognizable Date
wmfexcel 
This is a great tip. Not sure if this will help anyone else, but my challenge was having the output then drive other formula calculations. (i.e. select a date using the calendar and have that date work as a formula input into many formulas) My solution was to use the DATEVALUE function pointed at the date picker output. All of my other formulas were then referenced to the DATEVALUE output. Everything now updates as desired based on the date selected in the dropdowns. Maybe there’s a better way, but this worked for me.
LikeLike
Great tip! Thanks for sharing! 😀
LikeLike
Do you have video that talks about inser times instead of dates. Thank you
LikeLike
Unfortunately no…
I don’t know if there is such thing.
LikeLike
Thank you for your answer.
LikeLike
Is it possible to duplicate this? I need a date picker in each line of a 150 line template, so that each line can have it’s own chosen date (not the same date down the 150 lines). Make sense? Is there a way to copy this process or would you need to do it for each of the 150 lines to make it work?
LikeLike
Hi Amy,
Try this addin by Ron de Bruin
http://www.rondebruin.nl/win/addins/datepicker.htm
Hope it helps you.
LikeLike
Hello
i can’t seem to find the “Microsoft Date and Time Picker Control anywhere in the ‘more options’ option :((
LikeLike
Hey here is a good video that shows how to install this “tool”, I just followed it and it worked!
LikeLiked by 1 person
Hi Andy, Thanks for sharing! 🙂
LikeLike
Those of us who have to work within the restraints of corporate firewalls and IT access permissions may not be able to use these downloads. I solved my problem by creating a data validation list containing a column of numbers from 1 to 190 and the adjoining column with the formula =today() plus the number in the adjacent column’s cell. This does not give me a true “calendar”, but it does provide me with a list of dates that is quick to scroll through.
My application needed two date cells with different ranges, so in the data validation list I just selected the date formula cells that I needed. Using the =today()+(number 1 to 120) provides me with a list of dates that changes every day.
LikeLike
I made a nice example about it :
Excel Date Adding Form –
Enter the dates into cells easily with date userform.When double-clicking on any page ,the date form opens. You can enter the date format (short date,long date etc.) to you want the selected cells.
Source To Download : https://netmerkez.wordpress.com/2015/07/11/1130/
LikeLike
Thanks for sharing, netmerkez.
LikeLike
When formatting in the properties window for some reason, those formats are lost after saving them and exiting the sheet? for example, in properties I formatted the value to Calibri 9 font, save it and exited. when came back in, it reverted to an Ariel 12 font? How do I manage to save FONT changes while in DESIGN MODE after I exit??
LikeLike
Hi Bryan,
I can’t reproduce your case. The format is saved…
LikeLike
Yes, I am having the same problems. Infuriating. have spent hours trying to resolve.
Any luck
LikeLike
whoah this blog is wonderful i like studying your posts.
Keep up the good work! You understand, many people are looking around for this
information, you could help them greatly.
LikeLike
Just came across another cool Add-ins. If you are looking for Add-ins for drop-down Calendar, here’s one by Ron de Bruin.
http://www.rondebruin.nl/win/addins/datepicker.htm
You will also find many practical add-ins there.
LikeLike
If it were for a range of cells?
LikeLike
Hi Marcelo,
If that’s the case, I am afraid you need to add the DTPicker one by one.
Cheers,
LikeLike
What version of Excel is required? I can’t seem to find this particular ActiveX control in my list?
LikeLike
Hi Paul,
You need to display the “Developer” Tab first, if you are using Exce 2007 or later version.
If you are using Excel 2003, right-click on the menu tool bar, select “Control Toolbox”, there you find the “More Controls”.
Cheers,
LikeLike
Thank xou very much for this very good explaining! it helped me a lot!!! 😀
LikeLike
Hi Lily,
Thanks for your kind words. Glad it helps!
Cheers,
LikeLike
Is it possible that when I click on a cell in excel It displays a calender for selecting Date
LikeLike
Hi Anup,
Without using the DTpicker, I do not know if there is another direct way to display a calendar for selecting date. Maybe it can be achieved by VBA. However that is not my expertise. 😦
LikeLike
Hi Anup,
I came across this site where you may download a free “ExcelFort Popup Calendar Add-in”
http://excelfort.com/2015/01/10/popup_calendar_excel/
Although I haven’t tried that out, it looks good. See if it helps you!
Cheers,
LikeLike
Thanks for your response, I tried to insert two DTPickers on different sheets in the same book, but I have not been able to do so!, second one affects the function of the first one!
LikeLike
Hi Ricardo,
How the second one affects the first one?
I just tried to insert more than one PTickers on different sheets. They works fine as usual.
LikeLike
how many DTPickers can I insert in the same book? Just one? Or as many as I want?
LikeLike
Hi Ricardo,
Although I do not know what is the maximum number of DTPicker we can insert in the same book, I am pretty sure we can have more than one. 🙂
Cheers,
LikeLike
Hi,
I am working with Microsoft Office Professional Plus 2013.
I am able to create the drop down calendar, assign the output to a cell.
It works fine until I save and reopen the workbook.
I got a fixed image with an un-functional down arrow.
The functionality comes back if I click on the “Restore down” bottom and then I maximize it
I would like to have the functionality without warning the user to click the above bottoms
Thank you in advance
Roberto
LikeLike
Hi Roberto,
Thanks for you feedback. I have experienced that same thing but honestly I have no idea why it happens.
I didn’t pay much effort in finding the reason as the problem can be fixed easily by changing the zoom level. I do it by holding CTRL key and then scroll up and down using mouse; which basically has the same effect of your action “restore and maximize”.
Should you want to find out the reason, I would suggest you post a question in http://www.mrexcel.com/forum/excel-questions/
where a lot of experts are willing to help.
Cheers,
LikeLike
Thank you, MF for your advice.
“Two in distress makes sorrow less” does not help, but it is a relief knowing that it may be a bug in the software
Regards
Roberto
LikeLike
Hi Roberto,
You are welcome.
FYI. I have experienced that kind of “bug” with other objects occasionally. An example is a worksheet with many charts. Every time I reopen the file, the size and the position of the charts change (only visually on the screen). And my solution is to change the zoom level and every charts resume normal.
Wondering if any one else has similar problem? and a solution ;p
Cheers,
LikeLike