How to Add Date Picker in Excel
Want to add a calendar date picker in Excel for easier date entry? This guide covers three methods: Data Validation for date restrictions, ActiveX controls for drop-down calendars (Windows), and VBA solutions for full calendar pickers.
Quick Answer
Excel does not have a built-in visual date picker. The easiest approach is to use Data Validation (Data tab > Data Validation > Allow: Date) to restrict entries to valid dates. For a pop-up calendar, use the ActiveX Date and Time Picker control (32-bit Windows only) or create a VBA UserForm calendar.
Quick path: Data tab > Data Validation > Allow: Date > Set your date range.
Method 1: Data Validation (All Versions)
Data Validation is the simplest way to ensure users enter valid dates in Excel. While it does not show a visual calendar, it restricts input to valid dates and can display helpful input messages.
Step-by-Step Instructions
- Select the cell or range where you want date entry.
- Go to the Data tab on the ribbon.
- Click Data Validation in the Data Tools group.
- Under "Allow," select Date.
- Choose your criteria:
- between: Set start and end dates
- greater than: Only allow dates after a specific date
- less than: Only allow dates before a specific date
- equal to: Allow only one specific date
- Enter your date values in the Start date and End date fields.
- Click OK to apply.
Adding an Input Message
Make date entry more user-friendly by adding an input message that appears when the cell is selected:
- In the Data Validation dialog, click the Input Message tab.
- Check "Show input message when cell is selected."
- Enter a Title (e.g., "Enter Date").
- Enter an Input message (e.g., "Please enter a date between 1/1/2026 and 12/31/2026").
- Click OK.
Pro Tip: Use cell references for date ranges instead of typing dates directly. For example, enter =A1 as the start date if A1 contains your minimum date. This makes ranges dynamic and easier to update.
Method 2: ActiveX Date and Time Picker (Windows 32-bit)
The Microsoft Date and Time Picker Control provides a visual drop-down calendar. This is the closest thing to a built-in date picker, but it only works on Windows with 32-bit Excel.
Important: This control requires 32-bit Excel on Windows. It is not available on 64-bit Excel, Mac, or Excel Online. If you have 64-bit Excel, skip to Method 3 (VBA) or use a third-party add-in.
Enable the Developer Tab
First, make sure the Developer tab is visible in your Excel ribbon:
- Right-click anywhere on the ribbon.
- Select Customize the Ribbon.
- In the right column, check the box next to Developer.
- Click OK.
Insert the Date Picker Control
- Go to the Developer tab.
- Click Insert in the Controls group.
- In the ActiveX Controls section, click More Controls (the wrench icon).
- Scroll down and select Microsoft Date and Time Picker Control 6.0.
- Click OK.
- Draw the control on your worksheet by clicking and dragging.
Link the Date Picker to a Cell
- Right-click the date picker control.
- Select Properties.
- Find the LinkedCell property.
- Enter the cell reference (e.g.,
B2) where you want the selected date to appear. - Close the Properties window.
- Click Design Mode on the Developer tab to exit design mode.
Now when you click the date picker, a calendar drops down. Selecting a date automatically enters it in the linked cell.
| Property | Description | Example |
|---|---|---|
| LinkedCell | Cell where selected date appears | B2 |
| Format | Date display format | 1 - dtpShortDate |
| Value | Initial/current date value | 1/11/2026 |
| Enabled | Whether control is active | True |
Method 3: VBA Calendar UserForm (All Versions)
For a date picker that works on all Excel versions (including 64-bit and Mac), you can create a VBA UserForm with a calendar. This method requires some VBA coding but provides the most flexibility.
Creating a Simple Date Picker Form
- Press Alt + F11 to open the VBA Editor (or Developer > Visual Basic).
- Right-click on VBAProject in the Project Explorer.
- Select Insert > UserForm.
- Add controls from the Toolbox:
- Add Labels for month/year display
- Add CommandButtons for previous/next month
- Add a grid of Labels or CommandButtons for days (42 buttons for a 6x7 grid)
- Write VBA code to populate the calendar and handle date selection.
Basic VBA to Show Calendar on Cell Selection
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Show calendar when cells in column B are selected
If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
If Target.Cells.Count = 1 Then
CalendarForm.Show
End If
End If
End SubAlternative: Use a Third-Party Calendar Class
Instead of building a calendar from scratch, you can download pre-built VBA calendar modules:
- Ron de Bruin's Date Picker: A popular free VBA calendar solution
- Sam Rad's Calendar: Works on both Windows and Mac
- Microsoft MonthView: Available in some Office installations
Pro Tip: Save your workbook as a macro-enabled file (.xlsm) when using VBA. Regular .xlsx files cannot store VBA code.
Excel 365 and Excel Online Options
Excel 365 does not include a native date picker control, and Excel Online has limited support for custom controls. Here are your options:
Excel 365 Desktop
- Data Validation: Works fully (Method 1 above)
- ActiveX Controls: Only on 32-bit Windows installations
- VBA UserForms: Works fully on Windows and Mac
- Add-ins: Install calendar add-ins from Insert > Add-ins > Get Add-ins
Excel Online (Web)
- Data Validation: Works, but limited customization
- ActiveX/VBA: Not supported in the browser
- Office Scripts: Can create automated date entry but no visual picker
Recommended Add-ins for Date Pickers
Several add-ins from the Microsoft Store provide date picker functionality:
- Go to Insert > Add-ins > Get Add-ins.
- Search for "date picker" or "calendar".
- Review options and install one that fits your needs.
- Follow the add-in's instructions to use it in your workbook.
Date Formatting in Excel
Once you have a date picker working, you may want to control how dates display. Excel offers many date format options.
Changing Date Format
- Select the cells containing dates.
- Press Ctrl + 1 (or right-click > Format Cells).
- Select the Number tab.
- Choose Date from the Category list.
- Select your preferred format or enter a custom format.
- Click OK.
| Format Code | Example Output | Description |
|---|---|---|
| m/d/yyyy | 1/11/2026 | US short date |
| dd/mm/yyyy | 11/01/2026 | European short date |
| mmmm d, yyyy | January 11, 2026 | Long date format |
| yyyy-mm-dd | 2026-01-11 | ISO 8601 format |
| ddd, mmm d | Sun, Jan 11 | Abbreviated with day |
Tips & Best Practices
Use Data Validation as a baseline
Even if you add a visual date picker, keep Data Validation on the target cells to catch any manually typed invalid dates.
Consider your Excel version
32-bit Windows users can use ActiveX controls. 64-bit users should use VBA or add-ins. Mac users need VBA or cross-platform add-ins.
Test before sharing workbooks
ActiveX controls may not work when shared with users on different systems. Test with representative users before distributing.
Keep date format consistent
Use the same date format throughout your workbook to avoid confusion, especially with international teams (US vs European formats).
Document custom solutions
If using VBA, add comments to your code and include instructions for users who may need to maintain the workbook later.
Consider alternatives for web sharing
If your workbook will be used in Excel Online, stick with Data Validation since VBA and ActiveX do not work in browsers.
Troubleshooting
Date and Time Picker Control Not Found
- 64-bit Excel: This control is not available in 64-bit Excel. Use VBA or an add-in instead.
- Control not registered: The mscomct2.ocx file may be missing. You can try registering it via Command Prompt (regsvr32), but 64-bit versions will not work.
ActiveX Control Grayed Out
- Protected View: Click "Enable Editing" if you see the yellow bar at the top of Excel.
- Macro security: Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and enable macros.
VBA Calendar Not Appearing
- Macros disabled: Enable macros in Trust Center settings.
- Wrong file type: Save as .xlsm (macro-enabled) instead of .xlsx.
- Form not called: Check that the Worksheet_SelectionChange event code is in the correct sheet module.
Dates Displaying as Numbers
- Cell formatting: Select the cells, press Ctrl+1, and format as Date.
- Linked cell issue: The ActiveX LinkedCell may output a serial date number. Format that cell as a Date.
Frequently Asked Questions
Does Excel have a built-in date picker?
Excel does not have a native pop-up calendar date picker. However, you can use Data Validation to restrict date entries, the ActiveX Date and Time Picker control (Windows 32-bit only), or create a VBA-based calendar form for a visual date selector.
How do I add a drop-down calendar in Excel?
To add a drop-down calendar in Excel, go to Developer > Insert > ActiveX Controls > More Controls > Microsoft Date and Time Picker Control. Draw it on your worksheet, then link it to a cell using the LinkedCell property. Note: This only works on 32-bit Windows Excel.
Why is the Date and Time Picker missing in Excel?
The Microsoft Date and Time Picker Control (mscomct2.ocx) is not included in 64-bit versions of Excel. It only works with 32-bit Excel on Windows. For 64-bit Excel, use a VBA UserForm calendar or third-party add-ins instead.
How do I insert a date picker in Excel 365?
Excel 365 does not have a built-in date picker control. You can use Data Validation for date restrictions, create a VBA calendar UserForm, or install calendar add-ins from Insert > Add-ins > Get Add-ins.
Can I add a date picker in Excel on Mac?
Excel for Mac does not support ActiveX controls. Mac users can use Data Validation for date entry, create VBA UserForm calendars (which work on Mac), or use cross-platform add-ins that provide calendar functionality.
How do I make a date picker appear when clicking a cell?
Using VBA, create a Worksheet_SelectionChange event that checks if the selected cell is in your target range, then opens a calendar UserForm. This provides the most user-friendly date picker experience in Excel.
What is the best date picker solution for sharing workbooks?
For workbooks shared across different systems, use Data Validation with clear input messages. ActiveX controls may not work on recipients machines. If a visual picker is essential, use a VBA solution saved in a macro-enabled file.
Summary
Adding a date picker in Excel depends on your version and requirements:
- Data Validation is the universal solution: Works on all versions, restricts input to valid dates, and requires no coding.
- ActiveX Date and Time Picker provides a visual calendar but only works on 32-bit Windows Excel.
- VBA UserForm calendars work on all versions and offer the most flexibility, but require macro-enabled files.
- Add-ins from the Microsoft Store can provide date picker functionality for Excel 365 users.
For most users, start with Data Validation for its simplicity and universal compatibility. Add a visual date picker only if your specific use case and audience require it.
Create Excel Tutorial Videos
Need to train your team on Excel date pickers and data entry? Deckoholic makes it easy to record screen tutorials with AI voiceover and professional polish.
Try Deckoholic Free