Dropdown calendar in Excel

Did you ever ask why Excel does not provide Dropdown Calendar for date selection? Like the one shown below:

Image

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 Image

  1. Go to File Tab (or Ribbon icon for Excel 2007)
  2. Go to Customize Ribbon
  3. Check the “Develop” on the right under Main Tabs
  4. OK
  5. There you go

Image

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

Image

Now you should see the DTPicker (Dropdown box) Image

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.

Image

To do this:

  1. Right Click the DTPicker (Make sure the “Design Mode” is ON)
  2. Select Properties
  3. 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 itImage

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:

Image

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

  1. If you want to edit or even move the DTPicker, you have to TURN ON the “Design Mode” first
  2. 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.

Image

Want more date-related topics?  Please read:

Update Your Excel Skills

Advertisements
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

35 Responses to Dropdown calendar in Excel

  1. Dan says:

    Do you have video that talks about inser times instead of dates. Thank you

    Like

  2. Pingback: How To Build Calendar In Excel | Africabank

  3. Amy Cribbs says:

    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?

    Like

  4. Asmahan says:

    Hello
    i can’t seem to find the “Microsoft Date and Time Picker Control anywhere in the ‘more options’ option :((

    Like

  5. Dustin says:

    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.

    Like

  6. netmerkez says:

    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/

    Like

    • MF says:

      Thanks for sharing, netmerkez.

      Like

    • Bryan Polick says:

      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??

      Like

  7. Lonna says:

    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.

    Like

  8. MF says:

    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.

    Like

  9. Marcelo Pinto says:

    If it were for a range of cells?

    Like

  10. Paul says:

    What version of Excel is required? I can’t seem to find this particular ActiveX control in my list?

    Like

    • MF says:

      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,

      Like

  11. lily says:

    Thank xou very much for this very good explaining! it helped me a lot!!! 😀

    Like

  12. Anup Sahay says:

    Is it possible that when I click on a cell in excel It displays a calender for selecting Date

    Like

  13. RICARDO Marin says:

    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!

    Like

    • MF says:

      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.

      Like

  14. RICARDO Marin says:

    how many DTPickers can I insert in the same book? Just one? Or as many as I want?

    Like

    • MF says:

      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,

      Like

  15. Roberto says:

    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

    Like

    • MF says:

      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,

      Like

      • Roberto says:

        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

        Like

        • MF says:

          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,

          Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s