Adding a Calendar to a Worksheet

by Allen Wyatt
(last updated September 20, 2014)

6

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:

  1. Display the Developer tab of the ribbon.
  2. Click the Insert tool, in the Controls group. Excel displays a palette of different controls you can insert.
  3. Click the More Controls option, which is the control at the very bottom-right of the options, under ActiveX Controls. Excel displays the More Controls dialog box. (See Figure 1.)
  4. Figure 1. The More Controls dialog box.

  5. Scroll through the list of available controls and choose Microsoft Date and Time Picker Control.
  6. Click OK. The dialog box closes and the mouse pointer becomes a crosshairs.
  7. Click at the upper-left of where you want the control to appear and drag the mouse to the lower-right corner. When you release the mouse button, the control appears on the screen.
  8. Click the Properties tool in the Controls group. (The Developer tab of the ribbon should still be visible.) Excel displays the Properties dialog box for the control.
  9. Click the Linked Cell property and change it to the address of the cell you want linked to the control.
  10. Close the Properties dialog box.
  11. Click the Design Mode tool in the Controls group.
  12. 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:

    http://stackoverflow.com/questions/15816014/how-to-install-mscomct2-ocx-file-from-cab-file-excel-user-form-and-vba
    

    In addition, Ron de Bruin provides some good advice on installing and using date pickers in various versions of Excel:

    http://www.rondebruin.nl/win/s8/win003.htm
    

    If you prefer, you can find an Office app that Microsoft offers through their Office app store, here:

    https://store.office.com/mini-calendar-and-date-picker-WA102957665.aspx?assetid=WA102957665
    

    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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Editing a Toolbar Button Image

Don't like the toolbar button images in your version of Excel? Don't fret; you can pull up the button editor and make changes ...

Discover More

Excel Filters and Filtering

Excel provides two ways to filter your data so that only what you want to see is displayed. Discover how filtering works and ...

Discover More

Detecting the Beginning of a Sentence in a Macro

Macros can make life easier, as they provide a fast and efficient way of processing text in a document. Such is the case if ...

Discover More

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!

More ExcelTips (ribbon)

Mouse Click Event in VBA

Need to know if a particular cell is clicked with the mouse? Excel has no particular event handler for clicking in this way, ...

Discover More

DOS from Macros

Need to run a DOS command from within one of your macros? The answer is the Shell command, described in this tip.

Discover More

Renaming Worksheets Based On a List

Renaming a worksheet within a macro is a relatively easy task. When you start renaming based on a range of names, though, the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 8?

2016-04-15 09:21:13

amy cribbs

I need a date picker on each line of a 150 line template, so each line can have it's own assigned/chosen date (not the same date in each line). Is it possible to duplicate this for multiple lines or would you have to do this for each line individually?


2014-12-31 13:35:23

Matt

I am having an issue where the Calendar will NOT update the linked cell but the linked cell WILL update the calendar.

Has anyone else ran into this or know how to fix it?


2014-09-22 04:51:59

Dave S

When I tried inserting the DatePicker directly on a worksheet, as well as the problem of a 'ghost image' of the controlI also had problems with it resizing itself irrespective of the properties settings (Excel 2010). I ended up using the MonthView control (similar but not identical to DatePicker, but still allows date picking) on a form, with a small form-control command button next to the date entry cell to open the form. We then found this worked on some PCs but not others, which we eventually traced to a legacy issue of Microsoft having issued an update which broke the additional ActiveX controls, followed by another update to fix them again - some users had the most recent updates, others didn't. So what we thought would be a helpful feature turned out to be a bit of a pain.


2014-09-20 15:04:19

Dave Hansen

One might also like to create a macro with a chosen Ctrl+(Key) that performs the transfer to any cell you may be working in.
Lets say the date picker is in cell C1
Macro would be Activecell.Value = Range("C1")
If you lock the top row and column in place you can then pick the date and then highlight the cell you want the date in and press the macro button chosen.


2014-09-20 09:27:33

jpbisani

When i open more control it shows
Listclass and next Microsoft External item picker. it dosenot show microsoft date and time picker


2014-09-20 09:04:42

Colin fursman

Hi I have been building a bills program for my daughter who works in a nursery the bills were calculated on a yearly basses and devied by 12
But know they want to charge for the days booked per month, I have found that if I download a free Callander and link it to the booking and bills program I can use it to produce the correct number of book able days for any month from any year and produce a correct bill for any child on any month


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.