How to Insert a Date Picker in Excel

Hemera Technologies/ 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.

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

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.

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.

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.

Drag anywhere in the current spreadsheet to draw the calendar.

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.

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.

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

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

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.

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

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

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

Most recent