by Allen Wyatt
(last updated September 6, 2018)
Bruce would like to know if there is a simple way of inserting a standard calendar into a spreadsheet so that dates can be easily selected. Fortunately, there are a couple of ways you can implement calendars.
Excel includes a built-in calendar control, but you need to be aware that the support for the calendar control has been a bit spotty between versions. In other words, you may implement the control in Excel 2007, but that same control may not work properly in Excel 2010 or Excel 2013. Backwards compatibility between versions may be questionable, as well. (Why? Only Microsoft knows, and they are not saying.)
Even so, here's how you can insert the built-in calendar control, in this case in an Excel 2010 worksheet:
Figure 1. The More Controls dialog box.
At this point the Date Picker control should be active in your workbook. Click on the down-arrow at the right of it and you'll see a calendar with which you can select a date. When you leave the control (by clicking someplace else on the worksheet), the linked cell (step 8) is changed to reflect the date you selected using the control.
You may notice some funky behavior with the Date Picker control. When I first added it to my worksheet and turned off Design Mode, Excel displayed an active control in the upper-left corner of the worksheet and a non-active control at the point where I drew the control. (It happened on two different machines using both Excel 2007 and Excel 2010.) I had to hop back into Design Mode (at which point the control in upper-left corner of the worksheet disappeared), click the control I drew to select it, and then stretch it to be very large. When I turned off Design Mode, the spurious copy of the control at the upper-left of the worksheet was gone and I could use the version I drew just fine. Strange behavior, indeed—and part of the frustration of working with Microsoft's calendar controls.
If you are using Excel 2013, there is a very good chance that the Date Picker control won't be on your system. (It isn't with all versions of Excel 2013, but if you have some of the higher-priced versions that include Access, it may be there.) If it is not on your system, you can try installing the correct control, but the process is not for the timid or faint of heart. And, besides, Bruce said he wanted something that allowed dates to be "easily selected," a definition which seems at odds with going through the convoluted steps to install the correct control. If you would rather install it, you'll want to read the following web page carefully, including the initial post which includes where you can download the control:
In addition, Ron de Bruin provides some good advice on installing and using date pickers in various versions of Excel:
If you prefer, you can find an Office app that Microsoft offers through their Office app store, here:
This Office app will only work with Excel 2013, but the buyer may want to beware: It hasn't received the greatest of reviews lately. But, for $1.99 you may want to give it a try.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13254) applies to Microsoft Excel 2007, 2010, and 2013.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Functions are a common programming construct. They help you to create easy ways of processing information and returning a ...Discover More
Want to know how much of a time difference there is between your machine and a different machine? This tip provides some ...Discover More
Macros are great when it comes to automating how you work with your workbooks. What if you want to fundamentally change ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.