How to Insert the Excel Date Picker for an Entire Column

What is Excel Date Picker?

The Excel Date Picker is a handy feature in Microsoft Excel. It provides a calendar pop-up that allows users to select dates with ease. You can find this feature in 32-bit versions of Excel 365, Excel 2019, Excel 2016, Excel 2013, and Excel 2010. However, it won’t work in any 64-bit version of Excel.


Step 1: Add Developer Tab

  • Click the File tab.

  • Select Options from the list.

  • Choose Customize Ribbon from the left side box.
  • Select Main Tabs from the right-side box.
  • Put a tick mark on the Developer box.
  • Press OK.

Add Developer Tab for Excel Date Picker

The Developer tool is now showing in the main tab.

Add Developer Tab for Excel Date Picker

 

Read More: [Solved!] Datepicker Not Showing in Excel


Step 2: Insert Excel Date Picker

  • Click on the Developer tab.
  • In the Controls group, select Insert.
  • Choose More Controls from the ActiveX Controls option.

Insert Excel Date Picker

  • Select Microsoft Date and Time Picker Control 6.0 (SP6) from the dialog box.
  • Press OK.

Insert Excel Date Picker

The Date Picker is marked. It extracts the date of the present day.

Insert Excel Date Picker

Read More: Insert Drop Down Calendar in Excel Without Date Picker


Step 3: Control and Customize the Date Picker

  • Right-click on the Date Picker to control its movement. As you displace the cursor, the date picker will move accordingly.

Control the Date Picker

  • To resize the Date Picker, place the cursor on its edge, press the mouse, and adjust the size.

Control the Date Picker

  • Access the Properties window by right-clicking on the Date Picker.

Control the Date Picker

Modify properties like length, width, and font type.

Read More: Make an Alternative to Datepicker in Excel


Step 4: Link the Date Picker with a Desired Cell

  • Right-click on the Date Picker and choose Properties.

  • In the Properties window, locate the LinkedCell row.
  • Specify the desired cell (e.g., Cell C5).

Link Date Picker with Desired Cell

  • Press Enter.

Link Date Picker with Desired Cell

  • Change the Checkbox property from False to True.
    • Go to the Properties window and choose True for the CheckBox row.
    • Press Enter.

  • View the VBA Code:
    • Right-click on the Date Picker and choose DTPicker Object > Properties.

Link Date Picker with Desired Cell

  • Check the Format and tick the CheckBox in the DTPicker Properties window.
  • Press Apply, then click OK.

Link Date Picker with Desired Cell

Now, look at the Excel file.

The date of the present day is shown on Cell C5.

  • Go to the View Code option of the Date Picker by pressing the right button of the mouse.

The VBA code is showing.

Link Date Picker with Desired Cell

This Date Picker is running based on this VBA Macro.

Read More: How to Use Date Picker in Excel 64-Bit


Insert a Date Picker for an Entire Column using VBA Macros

Step 1: Prepare the Worksheet

  • First, ensure that you’re working with a worksheet where you want to insert the Date Picker.
  • If you only need dates (without the time value), make sure your data contains only date values.

Step 2: Add the VBA Code

  • Go to the View Code option (usually accessible via the Developer tab).

  • Write the following code in the command module (VBA editor):
Private Sub Worksheet_SelectionChange(ByVal Dest As Range)
With Sheet1.DTPicker1
.Height = 20
.Width = 20
If Not Intersect(Dest, Range("C:C")) Is Nothing Then
.Visible = True
.Top = Dest.Top
.Left = Dest.Offset(0, 1).Left
.LinkedCell = Dest.Address
Else
.Visible = False
End If
End With
End Sub

Insert a Date Picker for an Entire Column

  • Save the VBA code (no need to run it yet).

Step 3: Enable the Date Picker

  • Return to the main Excel file.
  • If you’re in Design Mode (from the Developer tab), disable it.

Insert a Date Picker for an Entire Column

  • Click any cell in Column C.

Insert a Date Picker for an Entire Column

You’ll notice a small box appears next to each cell in Column C.

Step 4: Using the Date Picker

  • Click on the box next to a cell in Column C.
  • A pop-up calendar will appear, allowing you to select your desired date.

Insert a Date Picker for an Entire Column

  • To change the month, click on the month name.

  • Use the up-down arrows to adjust the year.

  • Fill in the desired date values throughout the file (you can set dates in any cell within Column C).

Read More: How to Use Excel UserForm as Date Picker


Things to Remember

  • Date Picker is not available in 64-bit versions of Excel.
  • You cannot insert dates while in Design Mode.
  • Save the file as a Macro-Enabled Workbook (.xlsm).
  • You can change dates using the arrow buttons on the calendar.

Download Practice Workbook

You can download the practice workbook from here:


Related Article


<< Go Back to Excel Date Picker | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

2 Comments
  1. This is very well written thank you very much. My problem is that when using the date picker it reverts back to the format showing time.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo