Excel Date Picker – Table of Contents
Date Picker in Excel
As we have all gone through entering dates in MS Excel for various purposes like Invoice Date, Purchase Order Date, Delivery Date, Joining Date, etc. While entering the dates, we have to maintain integrity as there are various formats to enter dates like:
MS Excel considers the date as the number in the background. The first day in the calendar of MS Excel is 1 Jan 1900, Sunday which is considered as 1 by MS Excel. We need to be careful that it should not be alphabetical when entering dates as it will be considered as text.
Sometimes, we want to know the day falling on a particular date while we entering the date. For this, we have to open the calendar manually which is again time consuming and irritating task.
For resolving all these types of issues, we can use MS Excel Date Picker (Drop Down Calendar) to enter dates.
To insert Drop Down Calendar, we will use an ActiveX Control which is ‘Microsoft Date and Time Picker Control 6.0 (SP6)’.
How to Insert Date Picker in Excel?
Let’s understand an example of excel date picker.
Suppose, we have to maintain data for employees of a company. There are several fields like
- Emp Code
- Emp Name
- Emp Joining Date
- Emp Department
To enter data in MS Excel, we have created the following format.
To enter Emp Joining Date, we need to create a drop-down calendar so that it would be easier for the user to enter joining dates.
To create a drop-down calendar, steps are given below –
If the ‘Developer’ tab is not visible, below are the steps to follow to make the same visible.
Step 1: Under the ‘File’ menu, choose ‘Options’
Step 2:A dialog box named ‘Excel Options’ will open. Choose ‘Customize Ribbon‘ from the left edge of the dialog box. Checkbox for ‘Developer’ tab and click on ‘OK’.
Step 3:Now we can see the ‘Developer’ tab at the last of the ribbon.
Step 4: Choose ‘More Controls’ from ActiveX Controls.
Step 5: Choose ‘Microsoft Date and Time Picker Control 6.0 (SP6)’ from the list and click on ‘OK’.
Step 6: Click anywhere on the worksheet to create the drop-down calendar.
Step 7: Right-click on ‘Date Picker’ and choose ‘Properties’ from the list.
Step 8: Change the value from ‘False’ to ‘True’ for ‘CheckBox’ property so that null values can also be accepted. Close the ‘Properties’ dialog box.
Step 9: Right-click on Date Picker again and choose ‘View Code’ from the contextual menu.
Step 10: In ‘Visual Basic Editor‘, we can see that some code is already written. Replace the code with the following code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Sheet1.DTPicker1 .Height = 20 .Width = 20 If Not Intersect(Target, Range("C:C")) 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
Step 11: The first statement in the code tells the MS Excel Compiler to run the code whenever a new cell is selected (Selection is changed). The selected cell is sent to the Sub procedure as ‘Target’.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Step 12: These statements set the height and width of the Date Picker to 20 points in excel. It can be observed that we have used ‘With’ operator so that we do not need to refer to DTPicker1 again and again.
With Sheet1.DTPicker1 .Height = 20 .Width = 20
Step 13: The following ‘If’ clause sets the criteria that if any cell is selected in the ‘C’ column, only then Date Picker gets visibility. We have used ‘Intersect’ function as this function checks whether we have selected any cell in the C column then this function will return the address otherwise the value would be null.
If Not Intersect(Target, Range("C:C")) Is Nothing Then .Visible = True
Step 14: ‘Top’ property of Date Picker is set as equal to ‘Top’ property value of Selected cell. It means that this will go along with the upper border of the selected cell.
.Top = Target.Top
Step 15: This statement sets the left property of the Date Picker as equal to that of next right cell of the selected cell (Distance of left border of the D column from the extreme left of the worksheet). To get the reference of next right cell, we have used ‘Offset’ function with 0 as row argument and 1 as column argument as this will get reference to the cell in the next column.
.Left = Target.Offset(0, 1).Left
Step 16: This statement links the Date Picker with the Target cell so that whichever value is selected in the drop-down calendar that is displayed in the cell.
.LinkedCell = Target.Address
Step 17: The ‘Else’ statement tells the compiler to not display the Date Picker when any cell except that in C column is selected.
Else .Visible = False
Step 18: In the end, we close the ‘If’
Step 19: At last, we need to close ‘With’
Step 20: Now, Sub Procedure will end.
Make sure we save the file with ‘.xlsm’ extension as it saves the VBA code we have written and we will be able to run this code when any cell is selected in the C column.
Now whenever we select any cell in the ‘C’ column, we can see drop-down calendar at the top right corner of the selected cell. We can open the drop-down calendar by clicking on the down arrow symbol.
We need to click on any date in the calendar in the selected month to enter that date in the selected cell.
We can change month to previous or next using the arrow button placed at the left and right side of the calendar.
We can also click on the month to choose a month from the drop-down.
We can also change year by clicking on year and then using up and down arrows to choose the required one.
Things to Remember about Excel Date Picker
- ‘Microsoft Date and Time Picker Control 6.0 (SP6)’ is not available for the 64-bit version of MS Excel.
- After writing VBA code, we need to save the file with ‘.xlsm’ (Excel Macro-Enabled Workbook) extension otherwise VBA code would not run.
This has been a guide to excel date picker. Here we discuss how to insert date picker in excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –