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.

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

  1. cholo4u2 says:

    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. ram says:

    will i get whole excel text book in online

  3. Srinath says:

    Awesome..! Thank you so much..!

  4. Sandipan says:

    I find a problem sir :)……the control always returns MM/dd/yyyy but i need dd/MM/yy can you solve plz?

  5. Mike A says:

    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?

  6. Guest says:

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

  7. gennie says:

    Great thanks i shall try that!

  8. Rachel says:

    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!

  9. Naushad says:

    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.

  10. Elizabeth says:

    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

  11. Jakes says:

    Just Awsome ………without code

  12. Bronwyn says:

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

    • Santiago Lord-Laporte says:

      Hello,

      I am facing exact same issue.

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

      Santiago

  13. tito says:

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

  14. rajun69 says:

    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.

  15. rajun69 says:

    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

  16. Sharon says:

    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

  17. Sharon says:

    I will buy you beer…Your steps above worked like a charm. thanks

  18. Skafti Eliasson says:

    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 ?

  19. Bharat Sharma says:

    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.

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>