How to Insert a Date Picker in Excel (With Step-by-Step Procedure)

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:

Why Date Picker is Useful in Excel?

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.

📌 Steps

  • First, click on the File tab.
  • Next, click on Option.

Enable Developer Tab in Excel for the Date Picker

  • Now, from the Excel Options dialog box, click on Customize Ribbon option on the left side.

Enable Developer Tab in Excel for the Date Picker

  • From the right-hand side of the windows, select Main Tabs.
  • Finally, check the Developer box.

Enable Developer Tab in Excel for the Date Picker

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.

📌 Steps

  • First, go to the Developer tab.
  • From the Controls tab, click on Insert.

Insert a Date Picker

  • From the ActiveX Controls, click on the More Controls.

Insert a Date Picker

  • Now, select Microsoft Date and Time Picker Control 6.0 (SP6) from the More Controls dialog box.

Insert a Date Picker

  • 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.

Read More: How to Combine Date and Time in One Cell in Excel (4 Methods)


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.

📌 Steps

  • To make it larger or smaller, you can simply drag the date picker.

Customize 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.

Customize the Date Picker

  • 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.

Customize the Date Picker

Now, our date picker is almost ready. All we have to do is link the calendar to a cell.

Read More: How to Insert Date in Footer in Excel (3 Ways)


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.

📌 Steps

  • First, right-click on the date picker.

  • From the contextual menu, click on Properties.

Customize the Date Picker

  • 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.

Link the Date Picker Control to a Cell

  • If you right-click on the date picker and click on View Code you will see the VBA codes associated with it.

Read More: Excel Automatically Enter Date When Data Entered (7 Easy Methods)


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

📌 Steps

  • 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.

Insert Date Picker for a Single Column excel

Code Explanations:

  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.

Insert Date Picker for Multiple Columns 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.


Conclusion

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!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

6 Comments
  1. THank you so much. The STUPID explanation from Microsoft (imagine) wasn’t clear enough

  2. How do I get just the date and not the time to display in the linked cell?

  3. I tried using Microsoft Office Professional Plus 2013 and I could not find the Microsoft Date and Time Picker Control 6.0.

    Cheers

  4. Thanks for the explanation. But the number format is not applied to the date.
    Target.NumberFormat = “dd.mmm.yyyy” no matter if it is inside the VBA code or directly selected for the cell format.

    Thanks

    • Reply
      Naimul Hasan Arif Nov 7, 2022 at 11:31 AM

      Hi ISE. Thanks for your comment. Actually the date format is defined from the Number format. You can choose your date format from Number Format under the Home tab. There is no need to add VBA code to define date format.

Leave a reply

ExcelDemy
Logo