How to Insert a Date Picker in MS Excel

When you’re working on an Excel file, data consistency is important, but it could be difficult with dates. This is because dates can be entered in different formats.

Some people would enter the date in mm/dd/yyyy format. Others would enter the same date in dd/mm/yyyy format.

Using a date picker in MS Excel could prevent this problem.

2 Methods of inserting a Date Picker in Microsoft Excel:

  • Insert an object
  • Insert a date picker add-in

Let us begin by looking at how you can insert a date picker using the above methods.


Method #1: Insert an Object

You can use this method only if you use a 32-bit version of Microsoft Excel. Most modern PCs run the 64-bit version, though. If you use a 64-bit version, skip to the next method.

Step #1: Open your Excel workbook

If you already have an existing workbook where you want a date picker, open that. Otherwise, click on Blank workbook to create a new workbook.

Step #2: Add the Developer tab

Right-click on any empty space in the toolbar.

Click on Customize the Ribbon.

The Customize the Ribbon dialog box will open. 

Under the Main Tabs, tick the Developer checkbox.

Click on the OK button.

The Developer tab will be added to your toolbar.

Step #3: Select the More Controls icon

Go to the Developer tab on the main menu.

Click on the Insert icon, which will open a dropdown menu.

Under ActiveX Controls, click on the More Controls icon, which looks like a spanner.

TIP: Hover with your mouse over each icon to see its name.  

Step #4: Select Microsoft Date and Time Picker Control 6.0 (SP6)

The More Controls dialog box will open. 

Select Microsoft Date and Time Picker Control 6.0 (SP6).

Click on the OK button.

You’ll now see that the date picker has been inserted into your spreadsheet.

Step #5: Format the object

Right-click on the date-picker, a new menu will open.

Select Format Control… 

In the Format Control window that opens, you have options to change the size, protect it, or edit alt texts. 

In our example, we select the Move but don’t size with cells radio button.

Once done, click on the OK button.

Step #6: Link it to a cell

It is important to link the date picker to a specific cell.

Right-click on the object again, for the menu to open.

Click on Properties.

Enter the cell name in the LinkedCell field. 

In our example, it is B2.

Once done, a warning message will appear. 

Click on the OK button. 

Now, the object is linked to the cell you specified.


Method #2: Insert a Date Picker Add-in

You can use this method on a 32-bit version as well as a 64-bit version of Microsoft Excel. 

Step #1: Open your Excel workbook

If you already have an existing workbook where you want a date picker, open that. Otherwise, click on Blank workbook to create a new workbook.

Step #2: Add the Developer tab

Right-click on any empty space in the toolbar.

Click on Customize the Ribbon.

The Customize the Ribbon dialog box will open. 

Under the Main Tabs, tick the Developer checkbox.

Click on the OK button.

The Developer tab will be added to your toolbar.

Step #3: Select Add-ins

Go to the Developer tab on the main menu.

Click on the Add-ins icon.

Step #4: Add Mini Calendar and Date Picker

The Office Add-ins dialog box will open.

Click on the Store tab and search for ‘date’.

The Mini Calendar and Date Picker add-in will appear in the search results. 

Click on the green Add button next to it.

Click on the Continue button to add the calendar to your Excel workbook.

Step #5: Select the cell

Select a cell where you want to place the date picker.

Confirm your choice of cell by checking the Name box

For our example, we choose A1.

Step #6: Select the date from the date picker

After clicking on the cell, you need to select a date from the mini calendar. 

Click on a date and you will see that date in the selected cell.


Conclusion

If you already have the Developer tab in your MS Excel, then using any of the above two methods is quite quick. But you first need to know which method will work best for you, so please try them out. 

Leave a Comment