Insert a Drop Down Calendar Menu In Excel – Choose a Date!

So you want to create a drop down calendar in Excel? Apart from sending alerts to Outlook from Excel (working on that article as well) this has been the most requested how-to as of late. For example:

Reader Tim Asks:

How do I insert a calendar as a way for a user to click on the cell have the date box open and choose the date that should be in the cell? Also where do I send the beer?

Reader Wayne Asked:

I am sure that I saw a pull down menu in a worksheet cell.  I want to have a cell formated so that I can have a pull down menu of a calendar in which to choose the date.

This seems like it would be so easy yet when you look around on-line the answers are all full of VBA scripts and custom code.

I assume you just want someone using the spreadsheet to be able to select a cell, have a calendar drop down and then populate the field with whatever date the person chooses. Yes it can be done. Yes it is fairly involved. And yes, I have a workaround.

Would you like the easiest, cheesiest, sleaziest way to do this? Well then watch this:

The first thing you need to do is enable the Developer Tab in Excel.

  1. Select the Pearl or “Office Button” in Excel. The Pearl is the ball in the top left corner that gives you the option to Print, Save, etc.
  2. With the Pearl menu open select Excel Options from the very bottom on the right.
  3. Another menu will open – Select Popular
  4. Check the box for “Show Developer Tab in the Ribbon

Now you have the developer tab with all the controls for forms, xml, code, etc.

Go to the developer tab and select Insert, followed by the little Pink Floyd-esque More Controls icon.

Scroll through the list of controls and look what’s available. There are all sorts of funky add-ins you can use in your spreadsheets! We are looking for the Microsoft Date and Time Picker Control

Highlight it and select OK.

Your mouse will turn in to a cross hair – click and hold, draw a rectangle, release. You now have a date box!

You will also see code in the top akin to “=EMBED(“MSComCtl2.DTPicker.2″,””)”

You will also notice that you are in Design Mode because that icon is highlighted. This allows you to play with the settings for the date box you just created.

Deselect Design Mode and you have a date control! Select the drop down and a calendar appears! Holy Cow that’s super cool awesome but guess what! It doesn’t do anything!

You can click it and change the date as much as you want but nothing else is changing in your spreadsheet!

Here is where the genius of my non-code writing laziness pays off. Ready?

What cell do you want the date to change in? Let’s say C5

  1. Ok, In B5 type SELECT DATE:
  2. In D5 enter the formula =C5
  3. Still got that calendar control visible like in the image above?
  4. Let’s go back to Design Mode by clicking it. Move the calendar control box up around the C3 area just to move it out of the way.
  5. Right Click in the calendar control and select Properties.
  6. A properties menu will open up
  7. Look for the field Linked Cell and enter C5 (that’s where we want the date change to happen)
  8. You might get an error message – ignore it!
  9. Close the Properties menu
  10. Deselect Design Mode

Here’s where it all comes together. .

  1. Select the Date Drop down and change the date
  2. You will see Cell C5 change to that date
  3. You will see cell D5 (which =C5) change as well. The D5 was just to ensure that C5 was actually showing a date.
  4. Repeat step 1-3 until you are as giddy as a little girl.

Last Question: How do I use this?

Well you can create as many of these drop downs as you like. You can tell each one what cell to update (Linked Cell) and you can move the Date Control right over the top of the field it is updating!  Think about that – Move the date picker right over the top of the cell it is linked to. Only you know the truth! There is no advanced code, you’re just hiding the data in C5 under the Date control that is updating or controlling C5! Then do any date calculations you need to do using C5 as the reference!

I told you it was sleazy, cheesy and easy!

I’ll make it even easier – here’s the spreadsheet I used to write this tutorial and take screen shots

Excel Drop Down Date Example (12k)

And Tim, You any any other reader can always send the beer to me by clicking beer mug at the top of the page.

This entry was posted in Calendar, Excel, Instructions, Reader Question and tagged , , , , . Bookmark the permalink.

173 Responses to Insert a Drop Down Calendar Menu In Excel – Choose a Date!

  1. Deepak Varghese says:

    How can i just creat an option of Time in "0800 – 0830" format. Would it be possible from same steps???????? Could you help me out in this also???? Thanks Daniel

  2. I am still newbie for Excel but thanks for this tip…

  3. OptimusCee says:

    Hey! This works in Excel 2010.
    Plus I found out that I could 'copy and paste' the control box if I need multiple on a sheet. Afterwards, I just modify the linked cell for each one.
    Neat.
    Thanks bro

  4. Realty South says:

    Amazing stuff! Going to require a bit of time to think over the points=D

  5. sim says:

    Good tip. Anyone got any ideas how to validate i.e. in comparison with date in another cell rather than an absolute date?

    Using method linked cell can be changed without validation working

  6. I think the link is broken. Can someone please share any other link?

  7. Paul says:

    Hi Daniel,
    Thanks for the great tip, can you also help me if the format of the linked cell can be Long date too ex: 9 June 2011,

  8. UGG Boots says:

    Snow boots UGG Boots is full of legend. Seemingly simple-minded but popular UGG Boots UK cartoon form the Eurasian land, while popular in the world the wind blowing. Once you wear UGG Classic Tall Boots, you will not want to take off Tall UGG Boots. Don't miss out, grab a bargain now! Check it Out!

  9. Pingback: How to create drop down list in excel 2010

  10. Kaleem says:

    In Excel 2003, I can find "Calendar Control 12.0" either.

  11. michael says:

    Can you suggest how to get this feature working in the automatic data form so computer illiterate operators can select a date?

  12. Tim Norton says:

    Saved the day.
    Thanks

  13. bindu says:

    till now it is ok that is crating calendar using activex but we are not able to link it to the other sheets when we are coping and pasting to other sheet it is not working can u please let me know how to solve this problem

  14. Sundar says:

    Thanks for your tips. Please note it is working well in windows 2003 system and windows xp. but when use the same function in windows 7 after installing .ocx file it show cross simple. Please help me..

  15. Debasish says:

    Whenever I am going to insert the box, an error msg appears “Cannot insert object”. Please help!!!

  16. Augustine says:

    I am running Excel 2010 but I can not see the date and time picker in the cotrol box, under … mroe cotrols.

    Thanks

    • Tina says:

      in 2010, as in my case, you click excel option > customize ribbon, you will see two panels show up, on the right panel, under main tab, you will see the developer tab unchecked, go ahead and check it and you will get it :)

  17. Michael says:

    Excellent Post. Worked fine in XL 07'. I ended up resizing the date box so all i see is the drop down arrow. I then placed that arrow just to the right of the linked cell. Now it works both ways; i can type a date in the linked cell or i can select the drop down arrow next to the linked cell to select the date instead.

  18. Reggie says:

    Awesome! It helped me alot saved me soo much time! Very good step by step explanation!

  19. andeas says:

    to add a calendar to alldeadsea website, would I use css?

  20. J. Boyd says:

    Daniel, I would like to create an email alert to notify me that an officer has 90 days until they are due for pistol training and shotgun training. I will be using my Outlook email account to receive this alert. How do I do this?

  21. J. Boyd says:

    By the way I'm using Excel 2007 for the shotgun and pistol training.

  22. Richard Hough says:

    When I drew the rectangle, excell gives me an error”Cannot insert object”.It doesn’t give me a date box. Please help

  23. mario says:

    i'm as giddy as a little girl….
    Thanks!

  24. Brenda says:

    Thank you very much.. This is just what I was looking for !!!

  25. Jon Groeneweegen says:

    Freakin’ FANTASTIC ! Just what I needed to work round my Access problem.

  26. Karin says:

    Thank you – that was awesome!!!!!! :)

  27. Manish says:

    Daniel, it really is "Holy Cow that’s super cool awesome" as you yourself said…! Thanks a lot bro…!

  28. Kirk says:

    Does this work on a Userform? Its not on the standard control list when creating a userform. Is there a way to add it?

  29. Mike says:

    At last an explaination I can understand.
    One question, how to make it default to the current day?

  30. shushek says:

    Thank you. this is good learning stuff

  31. Darweesh says:

    Thanks. it was new information for me.

  32. Tina Lin says:

    Thank you so much! That was easy!

  33. I am not able to find the Microsoft Date and Time Picker Control option in the said path…please sugest if any addin need to execute..

  34. kamil says:

    Hello ,i cant download Microsoft Date and Time Picker Control 6.0 (SP4) into my excel ,kindly any help thanks in advance

  35. Many thanks Daniel! This is a huge help for those of us FINALLY making the leap to Excel 2010, and having to wean off the old Access calendar control. :-)

  36. Guest says:

    good article. thanks

  37. gue says:

    good article. thanks.

  38. umar says:

    it is possible when i crate new separate sheet in excel with date and time is automatically add………?

  39. umar says:

    it is possible when i crate new separate sheet in excel with date and time is automatically add………?
    i mean new insert work sheet…which include date and time in top …..

  40. LITTLE66085 says:

    help!!!! I have followed the instructions on the drop down calender, but upon reopening, the calender drop down is duplicated in the upper left corner and won't work in either area until the design mode is toggled. The calender disappears in the corner until design mode is turned off……whats wrong? Excel 2010. By the way, it works fine on a blank worksheet.

  41. LITTLE66085 says:

    Another Excel 2010 question. Can I place a image(company logo) on one tab sheet and copy it automatically yo other sheets in the same workbook? The reasoning is to be able to changs images on one sheet and automically update others. Thanks!

  42. Kathleen says:

    Could you please tell me if there is a way to have the cell appear blank until you pick a date? Right now there always has to be a date there.

  43. Gary says:

    Absolutely brilliant – will I tell them the real truth, or bask in the glory…..?

  44. I don't see Microsoft Date and Time Picker Control in my Toolbox -> more options. I have office 2010. Can you please help?

  45. Iraqi Dinar says:

    I am sure that I saw a pull down menu in a worksheet cell. I want to have a cell formated so that I can have a pull down menu of a calendar in which to choose the date.

  46. Deepak yadav says:

    Hi, after adding items in drop down box i want to show some data with the items in drop down box as i select it. Please suggest me something…

  47. Tyrone says:

    Hello Daniel I can't find the date and time picker what shall i do

  48. Tyrone says:

    Microsoft Excel 2003

    Create a new workbook.
    On the Tools menu, point to Macro, and then click Visual Basic Editor.
    On the Insert menu, click Module.
    Copy the Visual Basic for Applications code below into the module sheet.
    On the File menu, click "Close and Return to Microsoft Excel."
    Click the Sheet1 tab.
    On the Tools menu, point to Macro, and then click Macros.
    Click CalendarMaker, and then click Run to create the calendar.

    Microsoft Excel 2007 and Excel 2010

    Create a new workbook.
    On the Developer ribbon, Click Visual Basic.
    On the Insert menu, click Module.
    Copy the Visual Basic for Applications code below into the module sheet.
    On the File menu, click "Close and Return to Microsoft Excel."
    Click the Sheet1 tab.
    On the Developer ribbon, click Macros
    Click CalendarMaker, and then click Run to create the calendar.

    NOTE: If the Developer Ribbon is not showing go to Excel Options and enable it. In Excel 2007 you will find it in the Popular menu and in Excel 2010 you will find it in the Customize Ribbon menu.

  49. Anju says:

    Thanks Daniel.

Leave a Reply

Your email address will not be published. Required fields are marked *