How to Insert a Date Picker in Excel

Written by darrin koltow
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Insert a Date Picker in Excel
Excel's "Inputbox" statement offers one way of inserting a date picker. (Hemera Technologies/AbleStock.com/Getty Images)

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.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

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

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

    Drag anywhere in the current spreadsheet to draw the calendar.

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

    Range("A1") = Calendar1

  9. 9

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

  10. 10

    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.

  11. 11

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

  12. 12

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

  13. 13

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

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.