(29 votes, average: 4.90 out of 6)
Loading ...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.
- 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.
- With the Pearl menu open select Excel Options from the very bottom on the right.
- Another menu will open – Select Popular
- 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
- Ok, In B5 type SELECT DATE:
- In D5 enter the formula =C5
- Still got that calendar control visible like in the image above?
- 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.
- Right Click in the calendar control and select Properties.
- A properties menu will open up
- Look for the field Linked Cell and enter C5 (that’s where we want the date change to happen)
- You might get an error message – ignore it!
- Close the Properties menu
- Deselect Design Mode
Here’s where it all comes together. .
- Select the Date Drop down and change the date
- You will see Cell C5 change to that date
- You will see cell D5 (which =C5) change as well. The D5 was just to ensure that C5 was actually showing a date.
- 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.
December 4th, 2008 at 12:05 pm
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.
December 4th, 2008 at 12:14 pm
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.
December 4th, 2008 at 6:44 pm
Excellent! Thanks for the additional tip! That's good to know!
December 5th, 2008 at 12:03 pm
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
December 5th, 2008 at 3:01 pm
I dont understand your question – can you be more detailed?
December 17th, 2008 at 10:05 am
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!
December 18th, 2008 at 11:16 am
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.
December 22nd, 2008 at 2:03 am
cant find the Microsoft Date and Time Picker Control option in your mentioned path!!!! what shall i do???
January 1st, 2009 at 6:58 pm
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!
January 6th, 2009 at 3:32 pm
Download Here: http://danielcurran.com/freeware/MSCOMCT2.cab
To unzip a CAB file look for my posting on 7-Zip
Run the batch file once unzipped
January 7th, 2009 at 8:03 am
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.
January 7th, 2009 at 10:29 am
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."
January 20th, 2009 at 7:57 am
I already downloaded the MSCOMCT2 folder and the 7-Zip but I don't know how to install the control itself. HELP!!
January 20th, 2009 at 2:05 pm
This has your answer! http://danielcurran.com/instru.....to-insta...
January 21st, 2009 at 2:40 pm
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!
January 21st, 2009 at 3:40 pm
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.
February 18th, 2009 at 3:57 pm
Anyway I can do the drop-down calendar with Excel 2003??
March 10th, 2009 at 1:38 am
This help me a lot! Many thanks! Any tip regarding the question above?
March 20th, 2009 at 11:11 am
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!
March 29th, 2009 at 9:51 am
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!
April 29th, 2009 at 7:05 am
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!!!
April 29th, 2009 at 11:07 am
Tom3 I believe the WINcalendar mentioned above : http://www.wincalendar.com can help with what you are looking for
April 30th, 2009 at 11:05 am
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?
June 9th, 2009 at 2:57 am
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?
September 17th, 2009 at 5:58 pm
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
October 27th, 2009 at 11:45 pm
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
October 28th, 2009 at 9:44 am
after I select the Microsoft date and time pick and save and click and hold i get a error message
Can not insert object?
November 25th, 2009 at 3:40 am
Can't Find date and time picker in more controles
January 14th, 2010 at 10:04 am
Me to need the same thing need it to fill all da way down!
January 14th, 2010 at 5:52 pm
Flawless. Thank you. Worked great with Excel 2007.
January 29th, 2010 at 5:30 pm
I WANT TO DO THE SAME THING, A CALENDAR IN EACH CELL FOR THAT COLUMN. I TRIED WORKING IT OUT THROUGH THE DATA VALIDATION WAY BUT GOT NOWHERE. IT JUST SPAZZED OUT. NO SIR, DON'T WANNA BE A PROGRAMMER JUST ENJOY THE BENEFITS. FOR THE LOVE OF SOFTWARE, HELP.
February 2nd, 2010 at 1:46 pm
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?
February 2nd, 2010 at 1:49 pm
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.
February 3rd, 2010 at 9:44 am
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?
February 4th, 2010 at 12:58 pm
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.
March 15th, 2010 at 1:07 am
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.
March 21st, 2010 at 5:30 am
I am Happy, just got what i was searching.
GOOD MAN.
March 29th, 2010 at 6:05 pm
Very useful. However, Microsoft Date and Time Picker Control not among "More Control List"Btw Im using Microsoft Offiice 2007. HELP!!!!!!!!!!!!!!!!!!!
June 1st, 2010 at 5:28 pm
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.
June 2nd, 2010 at 1:57 am
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
July 22nd, 2010 at 9:38 am
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
July 29th, 2010 at 6:17 am
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.
September 1st, 2010 at 12:12 pm
How do you 'run' the batch file?
September 1st, 2010 at 7:53 pm
Thanks Daniel…this is a great help for Excel users