How to Insert Excel Date Picker: 3 Best Options Step by Step

Microsoft’s original Excel Date Picker has two limitations: The date picker control does not work with the 64-bit version of Excel and requires a macro-enabled workbook that can make it impractical.
Excel Date Picker

This article will teach you step-by-step how to install and use the Microsoft Excel Date Picker. Because of the 32-bit limitation and VB code/macro requirement, we will also show you two alternative date picker controls that require no macros and work in 32-bit and 64-bit versions of Excel.

Excel Date Picker Calendars

Option 1 (good): Jump directly to details on how to install and use Microsoft Excel Date Picker

Option 2 (better): Jump directly to details on how to install and use Randy’s workaround method

Option 3 (best): Jump directly to details on how to install and use the XLNavigator Calendar Date-Picker

12 month excel calendar date picker

Continue reading to learn how to install Excel Date Picker Controls that do not require VBA or macros and work with 32-bit and 64-bit versions of Excel.

Table of Contents

Key Points

In Excel, the drop-down calendar is called Microsoft Date and Time Picker Control.

The Microsoft date picker control 6.0 (SP6) is not available in the 64-bit edition of Excel.

Use various arrow buttons to change the date, month, and year in the drop-down calendar.

Due to the 64-bit Excel compatibility issue, a workaround Excel Date Picker method is presented later in this article.

Because the preceding Excel Date Picker approach needs a macro-enabled workbook, third-party Excel Date Picker add-ins will be discussed later in this article.

why use excel vertical tabs?

Why is a Date Picker handy in Excel?

A Date Picker can be helpful in Excel for several reasons:

  1. A Date Picker field can help you quickly select a date from a drop-down calendar, making it easy and fast to input dates into your spreadsheet.
  2. Additionally, Date Pickers can help to ensure that data is entered correctly by only allowing users to select dates from the drop-down calendar rather than inputting dates manually.
  3. Hence, an Excel Date Picker Control can help reduce data entry errors.

If you frequently work with dates in Excel, Date Pickers can be a valuable tool to add to your spreadsheet. The drop-down calendar can save you time and help ensure data entry accuracy.

How to Insert Microsoft Date Picker Control for 32-bit Excel

First, make the Developer tab visible so we can follow the following steps to insert the Excel VBA Date Picker.

Step 1: Choose Options under the File menu.

Excel Options menu

Step 2: On the Excel Options dialog box, select Customize Ribbon on the left edge of the dialog box, check the Developer tab’s checkbox, and click OK.

Customize Ribbon menu in Excel

Step 3: The Developer tab will now display on the Excel ribbon.

Excel Developer Tab on ribbon

Now that the Developer Tab is available on the ribbon, we can go through the following steps to insert the Excel date picker in Excel:

Step 1: Click on the Insert tab in the Controls group and select More Controls from ActiveX Controls (bottom right of the list of controls).

Excel ActiveX More Controls menu

Step 2: Select Microsoft Date and Time Picker Control 6.0 (SP6) from the list and click OK.

Microsoft Date and Time Picker Control 6.0 SP6 option

Step 3: To create the drop-down calendar in Excel, click any location on the worksheet.

select MS Date Picker control

Step 4: Right-click on the date picker control and click Properties from the popup menu.

right-click for MS Date Picker Properties

Step 5: To give the user a shortcut for picking today’s date, change the CheckBox value from False to True and close the Properties window.

MS Date Picker Properties windows

Step 6: Also, while in Design Mode, right-click on the date picker control and click Properties again to customize the colors and appearance of the Date Picker as needed:

change Date Picker colors on Properties menu
change specific colors on parts of MS Date Picker from Properties window
Date Picker drop-down calendar in Excel

Experiment with the different Calendar sections and colors to customize the calendar’s appearance to your liking.

How to make the Microsoft Date and Time Picker Control pop up when selecting a date cell

Step 1: To control Microsoft Excel date picker visibility when clicking on a target cell, we will add some code associated with the worksheet. Under the Developer tab, click on the Design Mode tab.

Excel developer Design Mode tab

Step 2: Press Alt + F11 or right-click on the Sheet tab and select “View Code” to open the Visual Basic Editor

view VBA code editor in Excel

Step 3: Copy and paste the following code in the Visual Basic Editor window:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrExit
  If Target(1).HasFormula Then Exit Sub
  With Sheet1.DTPicker1
    .Height = 15
    .Width = 25
    If Target.Cells.Count = 1 Then
    ‘If Target.Cells.Count = 1 And Not Intersect(Target, Union(Range(“A2”), Range(“B:B”), Range(“D:D”))) Is Nothing Then
      If IsDate(Target(1).Value) Or IsEmpty(Target(1).Value) Then
        .Visible = True
        .Top = Target.Top
        .Left = Target.Offset(0, 1).Left
        .LinkedCell = Target.Address
      End If
    Else
      .Visible = False
    End If
  End With

  Exit Sub

ErrExit:
  ‘gracefully handle errors here
End Sub

Excel Date Picker code key points

  • The following code will let the Excel Date Picker appear as a drop-down calendar on any selected cell value that has no value or is a valid date value.
  • The date picker will not show if the selected cell contains a formula or is not a valid date.
  • All the date cells in the same sheet will be linked to the calendar cell in the above code.
  • To apply the date picker only on specific cells or columns, replace the above line of code “If Target.Cells.Count = 1 Then” with “If Target.Cells.Count = 1 And Not Intersect(Target, Union(Range(“A2”), Range(“B:B”), Range(“D:D”))) Is Nothing Then”
Excel Date Picker VBA code for entire sheet
Excel Date Picker VBA code for specified ranges on sheet

Excel Date Picker for Entire Columns or Specified Ranges

  • Using the “Union” function above, you can specify a specific cell, a particular column, or multiple columns and cell ranges that should link to the Date drop-down Excel date picker field.
  • In the above Union example, the Excel date picker will only appear on a particular cell “A2” and multiple columns, i.e., entire column “B” and entire column “D.”
  • So, a linked cell will display a small drop-down control. If the selected cell contains a date format value or has no value, the selected cell will be a linked cell of the calendar control, and when the user clicks on that drop-down list, the Excel calendar pop-up will appear with the selected Date.
  • Remember to click on the “Design Mode” tab under the “Developer Tab” on the ribbon if the calendar control does not appear or disappear when you click on a particular cell or date column.
  • You can tweak the existing code to fit your specific needs. That is the beauty of Excel and VBA.
  • The Excel Date Picker is ideal for data validation to ensure valid and correct dates.
  • The Date Picker also works with a custom date format
Excel Date Picker Month and Date quick navigation controls
Excel Date Picker Year quick navigation controls

Insert Date Picker in Excel Workaround Solution Compatible with 64-bit and .xls and .xlsx files

Suppose you must use an Excel Date Picker in a 64-bit edition of Microsoft Excel. In that case, you can employ Randy’s alternate popup date picker solution at Excel for Freelancers to show a cell value on a calendar in Excel. Select a date like today’s Date from a popup calendar box when the calendar appears. This brilliant workaround method is compatible with Microsoft Office 365 and all other versions of Excel.

Watch Randy’s video explaining how to use his free Excel date picker here.

Workaround Date Picker key points

  • Randy’s workaround solution utilizes a brilliant method of using multiple grouped Excel shapes to form an interactive calendar shape with assigned macros.
  • Similar to the Date and Time Picker code above, the Calendar shape will appear and disappear and link against selected cells.
  • This solution does not require selecting any ActiveX controls under the Developer tab, and no file has to be registered.
  • The popup date picker does require VBA code and, hence, must be a macro-enabled file.
  • It is surprisingly easy and elegant to use, and one can even change the color scheme of the date-picker calendar.
  • If you want to include a date picker in your workbook that you want to share with other users that do not depend on external files like the MSCOMCT2.OCX from Microsoft, then this workaround calendar shape method is a good solution
  • One caveat is that this calendar date picker does not allow you to change the year from the calendar quickly. If you prefer that option, select Microsoft’s Date Picker if you want to include the date picker in your spreadsheet.
  • However, with Microsoft’s Date Picker, you still have the 32-bit Excel version limitation and potentially need to register the MSCOMCT2.OCX file.
  • Therefore, you should consider a third-party Excel Date Picker and Calendar tool like the XLNavigator.
Randy's workaround date picker calendar solution showing when specific date cell is selected

XLNavigator Excel Date Picker for all versions of Excel and all Excel file extension types

If you are using Excel and are limited to the 64-bit version, or if you are using .xls non-macro workbook files, there is an excellent Excel date picker that you can use. This Excel date picker tool is part of our XLNavigator add-in for Excel. It is an Excel date picker fully compatible with both Excel bit versions and all file extension types.

With this solution, you do not need to install more controls, and no need to follow code examples to make it work. You do not need to be concerned with the developer box, design mode, or date picker Excel.

This Excel date picker provides a user interface for selecting dates in Excel, and the Excel date picker displays a calendar in a separate task pane. You can choose dates from the Excel date picker calendar by selecting a date on the calendar.

XLNavigator Date Picker calendar always showing in side pane
XLNavigator select cell and pick date from side pane calendar

The excel date picker also supports simultaneously selecting dates for a range of cells.

XLNavigator pick date for multiple cells simultaneously

Selecting a date in a cell will show the exact date on the date picker calendar. The Date Picker Calendar also makes it easy to navigate to different years in the past or future, while the workaround date picker solution above does not.

XLNavigator quick month navigation
XLNavigator quick year navigation

The Excel date picker is very easy to use, and it is a great way to select dates in Excel when you are limited to using the 64-bit version of Excel or using .xls non-macro enabled workbook files. The date picker will always be available in all your Excel spreadsheets without a version bitness limitation or file type extension and macros. The XLNavigator Excel Date Picker is lightweight, quick, and always ready to use when needed.

If you want to learn more about the XLNavigator Excel date picker or download the Excel date picker, you can go to https://kudutek.com/xlnavigator.

Considerations when choosing the best Pop Up Calendar in Excel

Microsoft’s Excel Date and Time Picker 6.0 control combined with VBA code is a highly customizable, elegant solution that one can manipulate only to show specific cells, columns, or ranges of cells. The in-cell checkbox that shows in the drop-down selector makes it a snap to apply today’s Date without having to display the full calendar and makes it less distracting and obtrusive. Yet the calendar pops up with a single click if you must select another date. This Date Picker also makes it easy to change to another year from the calendar.

This Excel Date Picker is smooth and responsive and is an elegant solution.

Microsoft’s Excel Date Picker’s cons are:

  • Excel 32-bit limitation. Although Office 365 can run as a 32-bit version on Windows 64-bit platforms, the default version is 64-bit, requiring extra steps to install the 32-bit version.
  • Therefore, we should assume that most users these days use 64-bit Excel, which means that you cannot share your Excel spreadsheet with the Microsoft Date and Time Picker with other users.

Randy’s workaround Date Picker solution, compatible with Excel 64-bit, is an ingenious way to offer users a very familiar date picker experience. This solution allows sharing Excel spreadsheets with other users regardless of their Excel bitness. The option to change the color of the calendar is a nice touch.

Calendar Shape cons are:

  • This method still requires sharing macro-containing files with other users. Workbooks containing macros pose a security risk and sometimes can be challenging to share in corporate environments.
  • Randy’s Date Picker does not allow the user to change years from the Calendar shape quickly.
  • Selecting a linked cell with the date picker can make the screen flicker before the calendar picker appears. It is not a big deal, but slightly less elegant than the Microsoft Date Picker solution.

The XLNavigator Excel Date Picker does not require additional code to manipulate its behavior. Instead, you make it visible on a side pane or hide it when unnecessary. When you select a cell with a value that can convert to a Date, it will reflect that Date on the calendar. When the user picks another date on the calendar, it will change the selected cell to match that Date. The XLNavigator Date Picker Calendar also lets you change the year or month quickly and easily.

XLNavigator cons are:

  • If you share your workbook with another user, they need to install the XLNavigator app so they will see the identical Excel Date Picker. However, they can still manually enter the dates as mere mortals do.
  • The free version of the XLNavigator tool does show some in-app ads.

Frequently Asked Questions

How do I determine which Excel date picker method to use?

It all depends on the following questions:

  • Do you want to share your spreadsheet with other users?
  • Do you want them to have the same popup Excel date picker experience with no action on their end?
  • If you wish to share the Date Picker behavior out of the box, will your users likely be on 64-bit Excel?
  • Is sharing a macro-containing workbook with your users problematic due to security?

Ultimately, only you can decide on which method to use. I like the Microsoft Excel Date and Time Picker control the best when wanting a user to experience the popup date picker experience I intended. Unfortunately, because Microsoft does not offer this control for 64-bit, that limits you to Randy’s workaround Excel Date Picker solution.

If you use the Date Picker Calendar for yourself and want it to always be available like a standard Excel feature, I would say the XLNavigator Date Picker is your best option. A bonus is that this solution includes additional features like Excel Vertical Tabs.

Excel 32-bit versus Excel 64-bit

Excel comes in two versions: 32-bit and 64-bit.

You can install 32-bit or 64-bit Office on a computer with a 64-bit operating system and an x64-based processor.

Here is a great article that explains the differences and reasons to choose between the 64-bit or 32-bit version of Office.

How to install 32-bit Excel with Office 365?

The Office installer will automatically install the same matching bit version as your host Operating system. To install the 32-bit version on a 64-bit OS, follow these steps:

Step 1: Click the “Install Office” link in Office 365 online and select the “Other install options.”

Install Office 365

Step 2: Under “Office apps & devices,” click on the “View apps & devices” option.

install office 365 for specific apps and devices

Step 3: Select the 32-bit option from the “Version” drop-down and click on the “Install Office” button to download the 32-bit installer.

Install Office 365 32-bit version

Step 4: Once downloaded, start the installation process as usual.

Installing Office 365
Office 365 apps installed

To verify your Excel bit version, open Excel and select “Account” under the “File” menu.

Excel Account menu

Then click on “About Excel”

About Excel tab

and on the About dialog box towards the top right, you will see the bit version and exact product version.

Verify 32-bit version of Excel on About window

How to register the Date Picker ActiveX Control

Step 1: Check to see if mscomct2.ocx already exists on your computer by typing mscomct2.ocx in the search box and clicking Enter.

If the mscomct2.ocx file does not exist, then continue to the next step. Otherwise, go to step 4.

Step 2: Download the Microsoft Common Controls 2 Object Library (MSCOMCT2.OCX) file.

If the Microsoft Common Controls 2 Object Library is not found on your PC, you can download it using this link. Please note the Microsoft Date and Time Picker control works only with the 32-bit version of Excel.

Step 3: Extract the mscomct2.ocx file to the appropriate folder:

Extract the mscomct2.zip file and place the mscomct2.ocx in one of the following locations, depending on your Windows bitness:

Windows 32-bit: C:\Windows\System32

Windows 64-bit: C:\Windows\SysWoW64

Step 4: Next, register the Microsoft Date Picker on your Windows system as follows:

(1) Open the Command Prompt as Administrator

(2) In the Command Prompt window, enter one of the following commands, and then press Enter:

Windows 32 bit: C:\Windows\System32\regsvr32.exe mscomct2.ocx

Windows 64 bit: C:\Windows\SysWoW64\regsvr32.exe mscomct2.ocx

(3) The above step should successfully install the mscomct2.ocx library on your computer.

How do I insert the Excel Date Picker into a whole column in Excel?

For the MS Date Picker, you would use VBA to control when and where the date picker will be made available. See the section How to make the Microsoft Date and Time Picker Control pop up when selecting a date cell earlier in this article.

On the other hand, if you use a tool like the XLNavigator Date Picker, it is always shown on the side.

Closing Remarks

Excel Online has limited options for date pickers and other controls. One option for using the date picker Excel online is the Mini Calendar Date Picker by Vertex.

excel mini calendar date picker
install mini calendar date picker add-in

Date Pickers are great for data validation to ensure accurate dates, thus eliminating a data validation error message when you enter invalid date values.

Select Microsoft Date Picker if you want complete control over the date picker placement and behavior and if your target users will be using the Excel 32-bit version.

If you want a date picker that always works without being bound to a specific workbook, bit version, or file extension, I would recommend using the XLNavigator Date Picker free version for you and your users.

Master Excel VLOOKUP Function: 10 Examples

LEARN SOMETHING NEW

Follow Us on The KuduTek Channel

Learn about Excel and SQL and all kinds of cool stuff!