We Value Your Privacy

We and our partners use technology such as cookies on our site to personalise content and ads, provide social media features, and analyse our traffic. Click below to consent to the use of this technology across the web. You can change your mind and change your consent choices at anytime by returning to this site.

Update Consent
Loading ...

How to Insert a Date Picker in Excel

Updated April 17, 2017

One reason Excel users work with this spreadsheet application is to enter data. One type of data they frequently need to enter in Excel is a date. Excel can recognise different types of date formats when they're entered, but manually typing a date is tedious. Excel, along with other Office applications, offers a calendar control to solve this problem. Installing this control in Excel involves first exposing the gallery containing it. After installing the control, it's up to the programmer to do something with the clicks the control receives from the user.

Loading ...
  1. Open Excel, then click the "Developer" heading. This heading has commands allowing you to install the calendar control.

  2. Click the "Design" button to enter design mode, then click the "Insert" button. Excel will display a gallery of controls you can install on your spreadsheet.

  3. Click the bottom right control, which is labelled "More controls". A dialogue box will appear, containing many additional controls you can add to the spreadsheet.

  4. Scroll to the "Calendar" control, then double-click it to enter the mode for drawing the calendar control. Your cursor will turn to a crosshair, indicating the mode for drawing the calendar.

  5. Drag anywhere in the current spreadsheet to draw the calendar.

  6. Click the "Design" button again to return to the run-time mode of the spreadsheet. Click the calendar control you just installed. Nothing happens, because you haven't written any code to process the input generated by the user clicking the control. You'll install some programming to process the clicked date next.

  7. Click the "Design" button again to re-enter design mode, then right-click the calendar control. Click "View code" to enter the Visual Basic programming environment. Visual Basic is the language Excel programmers use to create custom spreadsheet applications.

  8. Type the following code above the "End Sub" statement. This code fetches the date the user picks on the calendar control, then writes it to the cell "A1" on the current spreadsheet.

  9. Range("A1") = Calendar1

  10. Click the "Excel" icon on the Windows taskbar to return to Excel, then click the "Design" button to exit design mode.

  11. Click the calendar control. Excel will populate cell "A1" with a number each time you click the control. Notice, however, that the number doesn't appear to be a date.

  12. Click the "Home" menu, then click the cell "A1".

  13. Click the drop-down button in the "Number" panel. This button lets you choose how to format numbers.

  14. Click the "Short Date" item to format cell "A1" as a date. That cell now appears as a date.

Loading ...

About the Author

Darrin Koltow wrote about computer software until graphics programs reawakened his lifelong passion of becoming a master designer and draftsman. He has now committed to acquiring the training for a position designing characters, creatures and environments for video games, movies and other entertainment media.

Loading ...