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
- 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)”
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.
Mr. Excel shares a great tip in having a date picker in all versions of Excel! Check it out!
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