Given the diversity of date formats, installing a date selector is a good choice, especially for those who often deal with external documents.
But if you don't want to use third-party apps, here is how to implement one by using existing tools.
Note: The procedure described here is suitable for 32-bit Excel only. To find out whether you’re using a 32-bit Excel, click the Excel menu File > Account > About Excel.
- Open the Excel file that you want to add the date picker to.
- Make sure the Developer ribbon tab is visible.
If you don't see the Developer ribbon tab, you can enable it by clicking on File > Options > Customize Ribbon > Main Tabs > Developer.
- Click on the Developer tab and select Insert from the Controls group.
- From the ActiveX Controls section, select More Controls.
- Scroll down and select Microsoft Date and Time Picker Control 6.0 (SP6).
If there is no Microsoft Date and Time Picker Control 6.0 (SP6), you need to download it.
- Search online for mscomct2.ocx and download it.
- Right-click on the file you downloaded and choose Properties.
- Choose Digital Signatures and verify its digital signature is correct.
- If you're using 64-bit Windows, put mscomct2.ocx in this folder:
C:\Windows\SysWoW64\
If you're using 32-bit Windows, put mscomct2.ocx in this folder:
C:\Windows\System32\
- Right-click the Windows Start button, run Windows Terminal (Admin), and then enter the following command if your Windows is 64-bit:
regsvr32.exe C:\Windows\SysWoW64\mscomct2.ocx
- Restart Excel. Now you can find Microsoft Date and Time Picker Control 6.0 (SP6) in Excel.
- Click on OK to insert the date picker control.
- Now, click on the cell where you want to insert the date picker.
- Resize the control to a proper size.
- Right-click on the control and then choose Properties.
- In the Linked Cell field, enter the cell where you want the picker control to put the date value. In this case, the cell is "D3".
- Close the Properties window.
- Click the Design Mode button on the Developer ribbon tab to unselect it.
- Now, when you need to enter or change the date value of the cell that you have a date and time picker control linked to, click the "Pull Down" button of the control to pull down the calendar where you can pick a date value.
- Download the sample file: date-picker-the-free-way.zip.
The advantage of this method is that it is free.
The disadvantages of this method are obvious.
- This date picker control is an ActiveX control. Your company policy may disable it.
- It works with 32-bit Excel only.
- You must modify the Excel file to use it.
- It works only for the cell and file that you have modified. I.e., it does not automatically apply to other date cells and Excel files.
Our date picker add-in isn't free of charge after the full functional trial period. However, it overcomes the disadvantages of the ActiveX control. Our date picker is implemented as a COM add-in. It activates an icon button if a cell you select is a date cell, which you can click to open a multiple-month calendar. Visit the tutorial movie to find out how it works.