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.

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

  1. Thirtyeyes says:

    Well, that's a really neat trick. Do you have any advice on printing? Such as making the drop down date thing invisible while printing. I'd like the date on a printed form, but it shows both the linked cell and the drop down box. I can make the regular cell invisible, but not the drop down box and the drop down box has that thing on the corner.

    Thanks.

  2. Thirtyeyes says:

    Sorry, I should have looked before posting. In design mode right click and choose "Format Control" then "Properties" tab, just deselect the print object box and it won't show up when you print.

  3. dancurranjr says:

    Excellent! Thanks for the additional tip! That's good to know!

  4. jay says:

    Hi… I have 5 dates in a row and I want to bring them over to outlook calendar. Is it possible without going to outlook manually

  5. dancurranjr says:

    I dont understand your question – can you be more detailed?

  6. dancurranjr says:

    The website: http://www.wincalendar.com/has been brought to my attention as well. Take a look and I would appreciate feedback if it works for you!

    • Kevin says:

      Wincalender is a fantastic tool to use with Excel thank you for the suggestion.

    • Karen says:

      I looked at Wincalendar to see if it is easier that this ingenious workaround — and it is. You're a man after my own heart and I dearly love figuring out things like this, but Wincalendar works fairly well, from what I can see after 10 minutes.

  7. Ron Durand says:

    Sir,
    Microsoft Date and Time Picker Control (SP4) does not appear to be an option in the "More Controls" list on my work computer (Windows XP & Office 2007). Will also try this method using Windows Vista and Office 07 at home. Please adivise and thanks.

  8. sami says:

    cant find the Microsoft Date and Time Picker Control option in your mentioned path!!!! what shall i do???

  9. GrumpyHusky says:

    This is a FANTASTIC tip! Works like a champ. Is there a way to apply this formula to an entire column in a table, so that each row had a calendar date pick? Other than copying/pasting/modifying the properties for each row? Not that I am trying to avoid work…but, as you know, calculated columns allow you to insert rows in a table and the formulas are automatically applied to the new cells. Thanks for any advice!

  10. Pingback: How to Install Microsoft Date & Time Picker Control 6.0 (SP4)

  11. desiliciousdame says:

    Thanks Dan!
    I was trying to do this with Office 2007 and Windows vista (business version). The trouble of course was with vista and the security setting it has for activeX. So even though I allowed activeX in excel settings and windows feature settings, it still would not allow me to insert activeX control or view/edit an activeX object in a worksheet.
    So I had to use a workaround. Here goes:
    In developer mode, insert the activeX control called "Calendar Control 12.0". Then follow the same steps.
    This works in vista and win xp too.

    • dancurranjr says:

      Outstanding! Thanks for sharing that with everyone else: "In developer mode, insert the activeX control called "Calendar Control 12.0". Then follow the same steps."

      • TheAverageBear says:

        That's a good solution as long as you are not going to convert to Office 2010. The "Calendar Control 12.0" aka mscal.ocx will be deprecated in 2010 (that's Microsoft-speak for "gone").

  12. Oscar says:

    I already downloaded the MSCOMCT2 folder and the 7-Zip but I don't know how to install the control itself. HELP!!

  13. oscar says:

    How can I use more than 1 Drop Down Calendar on the same spreadsheet? I was trying but I couldn't make it. Thank you!

    • dancurranjr says:

      Look in the properties of the control and make sure each one has a unique name. Using multiple shouldn't be that difficult. If you can describe the exact problem you are having in detail that is a lot more helpful to troubleshoot it.

      • Hello Dan,
        I would like to have one calender on the sheet to pick the dates and in each individual cell, the date selected from the calendar only relates to the one cell.
        I am trying to do up a mtg schedule and require a different date in each row and am trying to make it simple to use. Understand that you can write a macro for this but i would like to avoid it if possible.

        Looking forward to your response – let me know if you need more info.

  14. Pingback: How To Create A Reminder Email For Outlook Email From Excel

  15. Adriana says:

    Anyway I can do the drop-down calendar with Excel 2003??

  16. IT Gurl says:

    This tutorial has been a fantastic help! However i have now run into a separate issue. The drop down calendars i have created continue to lose their formatting, whenever i close the document. When i re-open the document i am forced to go back into design mode, and resize the objects. Any ideas on how to fix this? I'm creating a form for use within our small office, and it must be completey user friendly. Thanks so much!

  17. Xanadari says:

    I believe I'm having roughly the same issue at IT Gurl, when I try to reopen the file after a few days of no use, the drop down calender(s) turns into a giant red X from which I can't choose anything. It's doing this on every page of the worksheet. Any ideas on how to fix this issue? Thanks for any help!

  18. Tom3 says:

    This is great, butI also have a related question – how do I have excel data displayed in a calendar form? It works importing data into Outlook, but it is not very functional so I was thinking about using Excel for the calendar but I do not know how to do it.

    For example – if I have a project that requires tracking of mulitple records I would like to visually see the schedule and progress. I have multiple columns of dates with associated tasks that I'd like to publish so people know what to do when. Can't use MS Project cause not enough people have it.
    Thanks for any help!!!

  19. steven says:

    I have a list of 8 cells that I want to insert a drop down calendar. I don't want the current date to show up in every cell. How do I not get the current date to show up in the cell and have it just be blank but be able to have a drop down calendar option?

  20. Rod says:

    As per IT Gurl, the rectangular shape of the object that I originally draw changes after re-opening the file to a square. If I go back into design mode to re-set the shape, the same thing happens again.
    How can I fix and lock the shape of the object so that it dose'nt keep resizing?

  21. EDGMTZ says:

    Many thanks I been loking for this for a long time. But with the difference that i am using it with a userform in VBA. Many thanks

  22. Maen says:

    hi, thanks for the great help.
    i have a questions. i need to protect the sheet and still people can select the date from the calender. it doesn't work even if i change the locked status in the properties to false?
    please help

    • Bhavin says:

      Hi Maen, did u get solution for this.
      How to make this calendar enabled when the sheet is protected.

      • ThatBloke says:

        Usually you just have to unlock the linked cell. This is how to ensure combo boxes and drop-downs work on protected worksheets/books. This is a very late reply but I only just got here!!

  23. patty says:

    after I select the Microsoft date and time pick and save and click and hold i get a error message
    Can not insert object?

  24. mohammad says:

    Can't Find date and time picker in more controles

    • Brian says:

      Make sure you are looking for "Microsoft Date and Time Picker…" These are listed in alphabetical order. Within the Developer tab on the ribbon, click Insert, then click the button right icon of the hammer and wrench (More Controls), and the date picker should appear in the list.

  25. Canon says:

    Flawless. Thank you. Worked great with Excel 2007.

  26. Brian says:

    I am having a problem although not with the functionality. I save my workbook as .xlsm. When I reopen the workbook, it prompts me to enabled the content which I do enable. After it is enabled, my date picker controls are displaying in a very odd manner (larger). When I scroll down in the spreadsheet then scroll back up they appear normal sized again. These date pickers control criteria within a query, and once I get this finished, I am going to distribute this to users. I would like the date picker controls to appear correctly. What could be going wrong? How could I fix it?

    • Curt says:

      Brian, To prevent the "Enable Macros" prompt, click the Office Pearl (as Daniel called it) and select Excel Options-Trust Center-Trust Center Settings-Trusted Locations and add the directory where your Excel book is stored. Check the box to include subdirectories. It's not a great solution, but is is a solution. (Office 2007). The other phenomenon appears to be a bug. I get it too.

    • johns_st says:

      Brian, did you get a response that solved your issue of the DTP control format and the need to scroll the page to make it appear correctly? I ma having the same issue and it wasn't until this post that I found the scrolling workaround. I was hoping that there was a solution to this issue!

      Thanks in advance for any assistance provided!

  27. Gurari says:

    I am having an odd issue in Excel 2003. I've successfully placed and formatted dtpicker. However, I save and close the document, and when I reopen one of the dates has resized and cannot be clicked on. When I scroll in the document, the problem goes away. Any solution to this bug so that I can open the document and not have to scroll to fix it?

    • johns_st says:

      Gurari, did you ever receive a response or a solution to the issue you have posted? I am having the same issue and am hoping for a resolution!

      Thanks in advance for any assistance provided!

  28. Brian says:

    Gurari, I just asked the same question and haven't been able to find a solution. I also posted this in an Excel forum and still no usable help. I'll let you know if I discover anything. Please let me know if you find out the issue as well.

  29. Cynthia Russell says:

    Thanks Daniel for a great tip. I experienced the same problems as Brian and Gurari above. I got around it by writing a macro that scrolls the page up and down everytime the workbook opens. As I just dable in macros a little, its probably a very pedestrian approach and there may be something better out there, but for now it works for me.

    • Chanaka says:

      Thanks Daniel…this is a great help for Excel users

    • Kevin 010 says:

      Cynthia, will you share that macro?
      Thanks!

    • johns_st says:

      Cynthia, I am having the same issue with regards to the problem that Brian & Gurari were having. Have you found a solution other than your macro to this issue. If not, would you be willing to share your macro with me?

      Thank you in advance for any assistance provided!

  30. hemanth says:

    I am Happy, just got what i was searching.

    GOOD MAN.

  31. jenny says:

    Very useful. However, Microsoft Date and Time Picker Control not among "More Control List"Btw Im using Microsoft Offiice 2007. HELP!!!!!!!!!!!!!!!!!!!

  32. Sean says:

    For those having problems with the datepicker resizing itself and becoming unclickable, I use VB code in the workbook to zoom out then zoom back in the window. This seems to be a temp fix for the resizing issue.
    To do this hold down ALT + F11, then double click on "This Workbook" found on left hand side, and it will show code on the right hand side: "Private Sub Workbook_Open()" under the following code:

    ActiveWindow.Zoom = 100
    Range ("A1:M3").Select
    ActiveWindow.Zoom = True
    ActiveWindow.scrollRow = 1

    In the range section enter what cells you wish it to zoom too. All this is doing, is forcing a resizing of the window. Hope this helps.

    • johns_st says:

      Sean, I tried your workaround but I'm not sure if I put it in correctly: I put it in as follows:

      Private Sub Workbook_Open()

      ActiveWindow.Zoom = 100
      Range("A1:T24").Select
      ActiveWindow.Zoom = True
      ActiveWindow.ScrollRow = 1

      End Sub

      I am running Excel 2010 if this matters. It doesn't seem to work as well as you described. Not sure if a permanent solution has been found to this dilemma either.

      Thank you in advance for any assistance provided!

    • Joni says:

      Thanks Sean,

      This fixed the issue for me as well. Was banging my head trying to get this to work. DTPicker always showed up in Upper Corrner and Cell A title. This forced scroll was a great catch.

      Thanks Joni

  33. Moby says:

    Thanks a lot Dan but i still have one question, i dont want the dropdown to display a single date but the dates for the whole month (in a cell)

    i guess what i am trying to say is the moment i select a month i want my spreadsheet to populate all the 30 days of the month in a column/cell

    can this be done

  34. Noemi says:

    Followed Sean's instructions and it works except when reopening the doc it shrunk so I always had to resize. Made a minor mod and now seems to work fine
    :
    ActiveWindow.Zoom = 100
    Range ("A1:M3").Select
    ActiveWindow.Zoom = True
    ActiveWindow.scrollRow = 1
    ActiveWindow.Zoom = 100

    • johns_st says:

      Noemi,

      I applied your code in to my VB window just as you showed but it didn't provide any different result. Do I have to place a "Private Sub…" line code and an "End Sub" line code for this to work?

      Thanks in advance for any assistance provided!

  35. Mike says:

    Help!! I am using Office 2003 and I have the DatePicker option under the Control Toolbox, and I can do all the steps, but the pull-down never works. When I go to click on it, I get the Cross Hairs like I am editing the Date Control Box I just created. Anyone have any ideas?

    Mike

  36. LPN Programs says:

    Great tut, Daniel. I tried the instruction to the T and well, I managed to get the drop down alright. But it would be very very cool to have a sort of "drop when cell is clicked" kind of calender for all cells. I think thats when I have to deal with all those codes, right? Anyways, thanks!
    BTW: the download link is not working

  37. Fayya says:

    Dear All, when i enter the data and time dropdown object, the sheet get errors while opening
    It creates an error while sheet is open saying that object printer settings1.bin have been removed while recovery. Have any ideas on how to solve this problem???

  38. AYA says:

    hi i want to create a pull down date menu that works like this; if i select a date it shows that date in the spreadsheet

  39. Zi. says:

    Thanks for the tip! This is really useful!

    Brian & Guari, that happened to me too. But i figured out that you just need to change the security settings to allow macros again. Then, it will work fine.

  40. Guest says:

    So, download cab file and extract ocx file to c:windowssysWOW64; then programs -> accessories -> command, right click, runas admin. In dos window, regsvr32 c:windowssysWOW64mscomct2.ocx. It works as detailed above for adding and manipulating the control. Only problem is, you can't use it to update individual cells; which is what I was looking for. Back to the drawing board.

  41. Sam says:

    Thanks Daniel, i tried the 'WinCalendar' Excel calendar & it works PERFECT. Plus it is free :-)

    See here: http://www.wincalendar.com/excel-pop-up-calendar….

  42. Red says:

    It worked great.

    Thx 4 sharing da knowledge!

  43. Sujai K says:

    Thank you very much Daniel for the great tip explained neatly.

  44. Mattv says:

    very cool! Question…

    can the properties make it so the cell is blank until a date is manually selected from the calendar?

  45. James says:

    Thanks Daniel,

    This is a neat tip.

  46. Jonathan says:

    How do I change the date display sequence to DD/MM/YYYY?

  47. Stephanie 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 alway has to be a date there.

  48. ahdkjdj says:

    Hi i am not able to get the calendar once i save and re-open it i get only a red color X symbol in the respective cell pls give me a solution.

  49. Deepak Varghese says:

    Hi Really worked for me and it's easier too. I just loved the way it explains for a layman in excel.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>