In Microsoft Excel, many essential tools create a great user experience. One of them is the Date Picker. With this tool, you can insert any date and time in a worksheet. It pops up like a calendar. You can select a date from that. In this tutorial, you will learn to insert a date picker in Excel with suitable examples and proper illustrations. A lot of details are coming in later sections. So, I hope you will stay tuned.
Download Practice Workbook
Why Date Picker is Useful in Excel?
Now, people love to work with user interfaces. It eases your work stress. How do we insert a date in a cell? By typing it in the cell, right? We all know typing is a hectic matter. What if you have 500 rows in a dataset? You wouldn’t like to insert all the dates manually in Excel!
Here comes the Date Picker to help us. It is a pop-up calendar that you can use to insert dates and control them. Take a look at the following screenshot:
You can see the date picker here. With this tool, you can choose any date and perform any operations in Microsoft Excel.
Step by Step Guide to Insert Date Picker in Excel
In the following sections, we will provide you with a step-by-step guide to inserting a date picker in Excel. We recommend you closely look and learn all these steps. It will obviously develop your Excel knowledge.
1. Enable Developer Tab in Excel for the Date Picker
First of all, this date picker tool is only available in the Developer tab. So, before you start, you have to enable the developer tab in Microsoft Excel.
So, let’s enable the developer tab first.
- First, click on the File tab.
- Next, click on Option.
- Now, from the Excel Options dialog box, click on Customize Ribbon option on the left side.
- From the right-hand side of the windows, select Main Tabs.
- Finally, check the Developer box.
As you can see from the Excel ribbon, we are successful in inserting the Developer tab in Microsoft Excel.
Read More: How to Insert Day and Date in Excel (3 Ways)
2. Insert a Date Picker
It’s time to insert the date picker in the worksheet. Follow these steps to do that.
- First, go to the Developer tab.
- From the Controls tab, click on Insert.
- From the ActiveX Controls, click on the More Controls.
- Now, select Microsoft Date and Time Picker Control 6.0 (SP6) from the More Controls dialog box.
- After that, click on OK.
- Finally, click on the cell where you want to insert the date picker.
As you can see, we have inserted a date picker control in the Cell.
When you insert the date picker control in the worksheet, you will see an EMBEDDED formula in the formula bar.
It means what type of control is implanted in this worksheet. Remember, you can’t change it. It will show a “Reference not valid” error if you do that.
3. Customize the Date Picker
You can see our date picker control is not looking good here. So, we have to customize it to give a better look.
When you insert the date picker, the Design mode is activated automatically. It allows you to modify it. Of course, we will do that. We will resize it and also change some of its properties.
- To make it larger or smaller, you can simply drag the date picker.
- While the Design mode is on, right-click on the date picker. After that, click on Properties.
- Here, you will see various options. We will work with a few of them.
- You can change the height, width, font, color, etc.
- Now, drag the date picker to the location of the cell where you want to place it.
Now, our date picker is almost ready. All we have to do is link the calendar to a cell.
4. Link the Date Picker Control to a Cell
You may think we have inserted it and can perform any procedure now. But here is a catch. You can do any operation without linking the date picker to a cell. Microsoft Excel won’t recognize the date associated with any cell automatically. Remember, no formula will work without this.
- First, right-click on the date picker.
- From the contextual menu, click on Properties.
- Now, in the Linked Cell option, type the cell reference you want to connect.
- When you select a date from the calendar, you will automatically see the date on the linked cell. Click on OK if Excel shows the “Can’t set cell value to NULL…” blunder.
- To accept Null values, change the value from FALSE to TRUE in the CheckBox.
- If you right-click on the date picker and click on View Code you will see the VBA codes associated with it.
How to Insert Date Picker on a Whole Column in Excel
Now, what we have done until now is insert a date picker in a cell. We can insert a date picker in a range of cells or a particular column. Whenever you click on the cell, a calendar will show up and you can choose a date from there. In the following sections, we will show you to insert both single columns and multiple columns.
1. Insert Date Picker for a Single Column
- To assign a date picker on a whole column, right-click on the date picker. After that, click on View Code.
- After that, you will see some code if you have customized it.
- Now, clear the VBA code and type the following code that we are showing here:
Sub Worksheet_SelectionChange(ByVal Target As Range) With Sheet1.DTPicker1 .Height = 20 .Width = 20 If Not Intersect(Target, Range("B:B")) Is Nothing Then .Visible = True .Top = Target.Top .Left = Target.Offset(0, 1).Left .LinkedCell = Target.Address Else .Visible = False End If End With End Sub
This code basically sets column B as a date picker.
- Now, deselect the Design mode.
- After that, click on any cell to remove the Date Picker.
- Now, click on any cell of column B. You will see date picker control from every cell.
With Sheet1.DTPicker1 .Height = 20 .Width = 20
This code demonstrates the sheet number (Remember your sheet number even if you have changed the name) and the date picker number. Here, we have sheet1(Basic Datepicker sheet) and date picker 1. Height and width that you set manually.
If Not Intersect(Target, Range("B:B")) Is Nothing Then .Visible = True
This code demonstrates that if any cell of column B is selected, the date picker will be visible. Or you can set a custom range like Range(“B5:B14”). It will set the date picker only for those particular cells in column B.
.Top = Target.Top .Left = Target.Offset(0, 1).Left .LinkedCell = Target.Address
The “top” property basically means it proceeds along with the upper border of the designated cell. It is equivalent to the “top” belongings value of the specified cell.
The “Left” property is equivalent to the next right cell (of the cell that you specified). It is the length of the left border from the outer left of the worksheet. We used the offset function to get the cell reference of the right cell.
“LinkedCell” connects the date picker with the target cell. When we select the date from the dropdown, it allows that in the cell.
Else .Visible = False
When you select any other cell rather than a cell of column C, the date picker won’t show up.
2. Insert Date Picker for Multiple Columns
Now, if you want to set multiple columns with a date picker, you have to make a simple change. Remember, before you set multiple columns with date pickers, you have to insert another date pickers again.
If you want to set a date picker for adjacent columns, you don’t have to write another code segment. Just change in the IF segment:
If Not Intersect(Target, Range("C:D")) Is Nothing Then
Now, the following code will set a date picker for columns B, D, E, G:
Here, we are not assigning the date picker in the entire column. Rather than, we are inserting it in a range of cells. Date picker 1 for B5:B14, Date picker 2 for D5:E14, and Date picker 3 for G5:G14.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Sheet1.DTPicker1 .Height = 20 .Width = 20 If Not Intersect(Target, Range("B5:B14")) Is Nothing Then .Visible = True .Top = Target.Top .Left = Target.Offset(0, 1).Left .LinkedCell = Target.Address Else .Visible = False End If End With With Sheet1.DTPicker2 .Height = 20 .Width = 20 If Not Intersect(Target, Range("D5:E14")) Is Nothing Then .Visible = True .Top = Target.Top .Left = Target.Offset(0, 1).Left .LinkedCell = Target.Address Else .Visible = False End If End With With Sheet1.DTPicker3 .Height = 20 .Width = 20 If Not Intersect(Target, Range("H5:H14")) Is Nothing Then .Visible = True .Top = Target.Top .Left = Target.Offset(0, 1).Left .LinkedCell = Target.Address Else .Visible = False End If End With End Sub
Look here, we have three date pickers here. One for column B, one for columns D and E combined, and another one for column G. After clicking each cell of these columns you will see a calendar. In this way, you can insert a date picker for multiple columns in Excel.
Big Issue With the Date Picker in Excel
If you are using 64 bit of any Microsoft Excel software or you are using Excel 365 or Excel 2019, you are already confused by now. It is because you couldn’t find the date picker in the ActiveX control.
We are sorry to say Microsoft’s Date Picker control is only available in 32-bit versions of Excel 2016, Excel 2013, and Excel 2010, but it won’t work on Excel 64-bit. So, if you really want to insert a calendar in your worksheet, use any third-party calendar. I hope Microsoft will bring some kind of date picker in the future.
💬 Things to Remember
✎ Make sure to link the date picker with a cell if you are working with one.
✎ Your file should be saved as a Macro-Enabled Workbook (.xlsm).
✎ To make any change to the date picker, make sure to select it from the developer tab.
✎ To see changes from VBA codes, deselect the date picker.
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to insert a date picker in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!