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.

174 thoughts on “Insert a Drop Down Calendar Menu In Excel – Choose a Date!”

  1. Hi, I am using Excel 2007 and want to do a VERY similar action but not exactly like what is described above. I would like to have a calendar pop up whenever I choose a cell within a given column the calendar is invisible all other times. I would like to choose a date via the calendar and have that date visible in the selected (active) cell. If anyone can help with this it would be greatly appreciated. Thank you! 🙂

  2. Worked just like it says on the box. Fantastic!!
    My only question now has to do with the property settings. I have managed to change font size type, Calender colours etc but can't seem centre the calender date in my created rectangle. Any hints?

  3. Is their a way to calculate number of days between two calendar dates when you are using the datepicker function?

  4. This worked great, but now it's freezing up my worksheet.

    Please tell me how to REMOVE it. I can't seem to figure it out.

    Any help would be appreciated!

    Many thanks!

  5. Thanks for the excellent work around. Another easy way of adding a drop down menu for dates in a cell falling within a year is to
    1. Add a list of all the dates of a month/s in a column.
    2. Then click on the cell you want to enter a date.
    3. Then go to Data tab> Data Validation > under Settings tab, in the Validation Criteria title, choose list from the drop down menu. Keep 'Ignore blank' and 'In-cell drop' down checked.
    4. Click on 'Source' field and select the cells containing the date range you had made in the beginning (Step 1)
    5. Click OK
    Now when you click on the cell you want to enter the date, a drop-down arrow appears which will allow you to select the date of your choice from the the range you had created.

  6. Part 2

    I am creating an Excel 2010 workbook for the employees to use as their Time Sheets. There are four date fields:
    1. Calendar for the Employee to select the date
    2. Drop-Down List of only Monday dates for current year
    3. Drop-Down List of all Calendar dates displayed in mm:dd format
    4. Time of Day Drop-Down List like Outlook has in its New Appointment window

    I was able to successfully complete all your instructions until the following instruction:
    Here’s where it all comes together. .
    1. Select the Date Drop down and change the date
    I do not have a Date Drop down displayed on any of the cells. The link to the spreadsheet you used does not work: Excel Drop Down Date Example (12k)
    So I was unable to see how yours displayed.
    Would you please help me so I can create all of the Date/Time Picker Controls I want for this workbook?
    Thank you for your help.
    Elizabeth

  7. Okay – so have successfully embedded the date and time picker and confirmed that the selected cells are updating in excel 2010 – When I send this spreasheet to another user however, the date picker is not usable. Works beautifully for me but useless for other users. It becomes a fixed image and the drop down is not functional. What are the next steps??

    1. Hello,

      I am facing exact same issue.

      Were you able to solve it? Your kind guidance would be tremendously appreciated.

      Santiago

    2. if a save the workbook, close it and then reopen it, the drop down calendar does not work. There is a fixed image. If I click on the “Restore Down” bottom and then on the “Maximize” bottom, the drop down is functional again.
      Is there a way to have it functional dummy-proof?

  8. is there a way of copying the date control tab to other cells because inserting one a time may be time consuming

  9. HI Daniel
    Amazing. Thanks.
    I am designing a training schedule for my students. Would i need to put in the calendar tool for each cell, and each course they are attending, for each student? So 14 courses, 50 students = 700 drop down calendars? Or can all be referenced off the one. My problem is students attend training on different dates.

  10. HI Daniel
    Amazing. Thanks.
    I am designing a training schedule for my students. Would i need to put in the calendar tool for each cell, and each course they are attending, for each student? So 14 courses, 50 students = 700 drop down calendars? Or can all be referenced off the one. My problem is students attend training on different dates.
    Thx
    Rajun69

  11. I have same request as Ranjan69. I’ve created a Sharepoint check in/check out monthly spreadsheet for our team; we needs to input dates in various cells, amount unknown. Is there anyway to have that one control work for all the cells in a particular column? thanks

      1. Just change it at linked cell as told by opening properties table
        Let say you want to put that calendar on F8 cell ..just put the same cell location on linked cell
        You can pretend that calendar which is in design mode is our master formula and we can change it as we decide by using linked cell

  12. Hi. Im trying this on exel 2010, I get 2 problems, the first i do not get the Microsoft date and time option and the second one i does not matter what i choose i get this “cannot insert object.”….. Someone here knows this problem ?

    1. I get the same issue, I’m using Office Professional Plus 2010 and running into the exact same error.

      HELP!!

  13. Hi,

    Its working fine when sheet is unprotected, but when i protect the sheet to prevent other details from the users calender does not work properly, linked cell not show the selected date again. how can i do it, plz help.

  14. THANK YOU!

    That just made me feel like a CHAMPION.

    I have been trying to get in a mini calendar for longer than I would like to admit.

    Thanks again!

  15. Hi Daniel
    I am using Office 365 and in this excel there no option of “Microsoft Date & Time Picker Control”…
    Please help me…

  16. Nice post. The config setup instructions are very clear.

    One issue: After saving the file with these changes done, then reopening, the date selector box no longer appears where it was setup.. say F5. It moves to the very top left corner of the worksheet above where the column A header would be. Like another post, it takes a Fullscreen change of excel to make the objects reappear properly.

    Anything that might be missing in the property setup ?

  17. I have created a diary for our Bowling club which runs from April to September on an excel spreadsheet. This diary is needed every year for the same period and requires me to change all the dates every year individually.
    Is there any procedure available that changing the first date in April will automatically advance all the dates in the diary up to September end. The dates are located in single cells and are batched in weeks.
    Any help would be most appreciated. Thanks

  18. The drop down Calenar is working fine. Thank you. I have to use the drop down calendar for all the months of the year to indicate the commencement of particular activities to follow. The Calendar installed always indicates a date, which might be misleading. I would like to precede the Calendar with “Select Date” signifying that the date is to be chosen in the following months. Can you please help me?
    Regards,

    Krishna Kumar

Leave a Reply to Rick Evans Cancel reply