Excel Date Picker for Entire Column

Get FREE Advanced Excel Exercises with Solutions!

Date Picker is one of the interesting tools of Microsoft Excel. It relieves us from the monotonous work of inserting data for each cell manually. Using the Date Picker tool, we get the facility to insert a date from a pop-up calendar. In this tutorial, we will discuss how to insert the Excel Date Picker and apply this for an entire column.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


What is Excel Date Picker?

Excel Date Picker is an interesting feature of Microsoft Excel. It offers us a calendar pop-up. So that, we can easily pick a date. This feature is available with 32-bit versions of Excel 365, Excel 2019, Excel 2016, Excel 2013, and Excel 2010. But it will not work on any 64-bit version of Excel.


How to Insert Excel Date Picker?

We will explain how to insert Date Picker in Excel step by step. Before inserting Excel Date Picker, we need to add the Developer feature in the Main tabs.


1. Add Developer Tab for Excel Date Picker

Before inserting Excel Date Picker, we need to add the Developer feature in the Main tabs.

Step 1:

  • First, press the File tab.

Step 2:

  • Select Options from the list.

Step 3:

  • Choose Customize Ribbon from the left side box.
  • Now, select Main Tabs from the right side box.
  • Put a tick mark on the Developer box.
  • Finally, press OK.

Add Developer Tab for Excel Date Picker

Notice the Excel file now.

Add Developer Tab for Excel Date Picker

The Developer mood is showing in the main tab presently.

Read More: How to Calculate Due Date with Formula in Excel


2. Insert Excel Date Picker

Now, we will insert the Date Picker in Excel. Apply the following steps carefully.

Step 1:

  • Click on the Developer tab.
  • Select Insert from the Controls group.
  • Choose More Controls from the ActiveX Controls option.

Insert Excel Date Picker

Step 2:

  • Select Microsoft Date and Time Picker Control 6.0 (SP6) from the dialog box of More Controls.
  • Then press OK.

Insert Excel Date Picker

Notice the image below.

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

Step 3:

  • Now, click on the Date Picker.

Insert Excel Date Picker

An EMBEDDED formula is shown on the formula bar. We can not change this formula.

Read More: How to Insert Current Date in Excel


Similar Readings


3. Control the Date Picker

Here, we will show how to control and customize a Date Picker.

Step 1:

  • Click on the Date Picker using the right button of the mouse. Keep pressing the button. As we displace the cursor, the date picker will move accordingly.

Control the Date Picker

Step 2:

  • We can resize a Date Picker. Place the cursor on the edge of the Date Picker. Keep pressing the mouse. Now, move the cursor and the size will change accordingly.

Control the Date Picker

Step 3:

  • We have the Property feature with the Date Picker. Press the right button of the mouse.
  • Choose Properties from the list.

Control the Date Picker

The Properties window offers the facility to modify the length, width, font type, etc.

Read More: Make an Alternative to Datepicker in Excel (2 Easy Methods)


4. Link Date Picker with Desired Cell

To properly implement a Date Picker, we need to link that with a cell. Here, we show how to link Date Picker with expected cells.

Step 1:

  • Take the cursor on the Date Picker.
  • Press the right button of the mouse.
  • Choose Properties from the list.

Step 2:

  • From the Properties window, go to the LinkedCell row.
  • We put Cell C5 here.

Link Date Picker with Desired Cell

Step 3:

  • Now, press the Enter button.

Link Date Picker with Desired Cell

We need to turn the Checkbox property from False into True.

Step 4:

  • Again, go to the Properties window.
  • Choose True at CheckBox row.
  • Press Enter now.

Step 5:

  • Now, put the cursor on the Date Picker.
  • Press the right button of the mouse. Choose DTPicker Object > Properties option.

Link Date Picker with Desired Cell

Step 6:

  • Choose the Format, tick on the CheckBox on the DTPicker Properties window.
  • Finally, press Apply, then click on OK.

Link Date Picker with Desired Cell

Now, look at the Excel file.

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

Step 7:

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

A VBA code is showing now.

Link Date Picker with Desired Cell

This Date Picker is running based on this VBA Macro.


Insert a Date Picker for an Entire Column with VBA Macros

We can apply Date Picker for different situations according to our needs. Here, we will explain how to apply this Date Picker for an entire column in Excel.

Step 1:

  • We will input only the dates, so delete the time value.
  • Now, go to the View Code option.

Step 2:

  • Write the code below on the command module.
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

Now, save the VBA code. No need to run the code.

Step 3:

  • Go to the main Excel file.
  • From the Developer tab, disable the Design Mode.

Insert a Date Picker for an Entire Column

Now, click any cell of Column C.

Insert a Date Picker for an Entire Column

A box is shown at each cell of Column C.

Step 4:

  • When we click on that box, a pop-up calendar is shown. We pick our desired dates from that calendar.

Insert a Date Picker for an Entire Column

  • Click on the month name if we need to change the month of the calendar.

  • We see an Up-down sign in the calendar. That is used to change the years of the calendar.

Now, fill all the desired date values in the file.

But we can set dates at any cell of Column C here.

Read More: How to Use VBA DateSerial Function in Excel


💬 Things to Remember

  • You will not find Date Picker in 64-bit versions of Excel.
  • You can not insert any date in Design Mode.
  • Must save the file as Micro-Enabled Workbook (.xlsm).
  • You can change the dates using the arrow buttons of the calendar.

Conclusion

In this article, we described how to insert a date picker in Excel. We also added an Excel date picker for an entire column. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Further Readings

Alok Paul

Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

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