• Curran@CurranOmniMedia.com

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

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 Comments

  • Deepak Varghese

    February 17, 2011 at 9:27 pm

    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

  • Bmi Calculator male

    March 9, 2011 at 4:14 pm

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

    OptimusCee

    March 19, 2011 at 5:44 pm

    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

    Realty South

    April 3, 2011 at 10:36 pm

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

    sim

    April 26, 2011 at 10:00 pm

    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

    Bryan Sterling

    May 6, 2011 at 5:08 pm

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

    Paul

    May 31, 2011 at 1:39 am

    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,

    UGG Boots

    June 30, 2011 at 12:23 am

    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!

    How to create drop down list in excel 2010

    July 14, 2011 at 1:09 am

    […] http://danielcurran.com […]

    Kaleem

    July 28, 2011 at 5:22 pm

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

    michael

    August 2, 2011 at 8:10 pm

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

    Tim Norton

    August 4, 2011 at 5:35 pm

    Saved the day.
    Thanks

    bindu

    August 10, 2011 at 7:22 pm

    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

    Sundar

    August 21, 2011 at 10:46 pm

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

    Debasish

    August 24, 2011 at 9:17 pm

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

    Augustine

    August 29, 2011 at 3:45 am

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

    Thanks

      Tina

      January 16, 2012 at 9:45 pm

      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 🙂

    Michael

    September 6, 2011 at 3:41 am

    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.

    Reggie

    September 19, 2011 at 7:53 am

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

    Reggie

    September 19, 2011 at 7:53 am

    THANKS!

    andeas

    September 26, 2011 at 9:06 am

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

    J. Boyd

    September 29, 2011 at 7:17 am

    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?

    J. Boyd

    September 29, 2011 at 7:19 am

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

    Richard Hough

    October 4, 2011 at 4:35 am

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

    mario

    October 4, 2011 at 10:39 pm

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

    Brenda

    October 6, 2011 at 6:45 am

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

    Jon Groeneweegen

    October 11, 2011 at 3:56 am

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

    Karin

    November 10, 2011 at 12:22 pm

    Thank you – that was awesome!!!!!! 🙂

    Manish

    November 11, 2011 at 4:53 am

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

    Kirk

    November 22, 2011 at 2:35 pm

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

    Mike

    November 22, 2011 at 5:31 pm

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

    shushek

    December 25, 2011 at 12:26 am

    Thank you. this is good learning stuff

    Darweesh

    January 4, 2012 at 11:46 pm

    Thanks. it was new information for me.

    Tina Lin

    January 16, 2012 at 9:46 pm

    Thank you so much! That was easy!

    Saurabh Parte

    January 18, 2012 at 2:31 am

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

    kamil

    January 26, 2012 at 6:12 pm

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

    Philip Mariconda

    January 26, 2012 at 6:34 pm

    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. 🙂

    Guest

    February 3, 2012 at 5:25 am

    good article. thanks

    gue

    February 3, 2012 at 5:25 am

    good article. thanks.

    umar

    February 8, 2012 at 2:45 am

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

    umar

    February 8, 2012 at 2:48 am

    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 …..

    LITTLE66085

    February 8, 2012 at 9:24 am

    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.

    LITTLE66085

    February 8, 2012 at 9:33 am

    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!

    Kathleen

    February 21, 2012 at 10:59 am

    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.

    Gary

    February 29, 2012 at 6:41 am

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

    Stephanie Bell

    May 2, 2012 at 12:02 pm

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

    Iraqi Dinar

    June 5, 2012 at 7:48 pm

    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.

    Deepak yadav

    August 10, 2012 at 8:57 pm

    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…

    Tyrone

    September 23, 2012 at 8:48 am

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

    Tyrone

    September 23, 2012 at 10:30 am

    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.

    Anju

    October 12, 2012 at 12:05 am

    Thanks Daniel.

Leave a Reply

%d bloggers like this: