Prefer watching a video tutorial? Watch how to insert a date picker in Google Sheets below:
Spreadsheet software like Google Sheets can be quite sensitive to differences in date formats, so it’s always better to pick the date from a real-time calendar, rather than typing it out.
In this tutorial we will show you how to insert a Google Sheets date picker, to allow your user to select their required date from a popup calendar.
Why Insert a Date Picker in Google Sheets?
There are many benefits to using a date picker in Google Sheets:
- It helps ensure that the date entered in a given cell is valid and in the correct format.
- It lets the user check the day of the week before finally entering the date
- It lets the user see if the date falls on a weekday or weekend.
- The user can check how many days the month contains or count how many days after their required date the month ends.
When Does the Google Sheets Date Picker Appear?
The date picker appears whenever the user double clicks on a cell containing a date value. However, for this to work, you need to insert a valid date in the Google Sheets cell.
Alternatively, you can simply type any number in a cell, convert the cell to Date format (by navigating to Format->Number->Date) and then double clicking on the cell. This will result in the date picker appearing next to the formatted cell.
How to Insert a Date Picker in Google Sheets
One way to make a popup calendar or date picker appear instantly is to type a date into a cell in the correct date format and then double click on the cell. This will result in a popup calendar appearing just next to the cell, allowing you to pick your required date.
However, we would usually prefer to have a date picker appear in a date cell, even if it is blank. For example, you might need a user to enter a start date and end date using the date picker, to ensure they enter a valid date in the correct format (say, if you’re looking to calculate the number of days between two dates).
For this, we can use the Google Sheets Data validation feature, as follows:
- Click on the cell or range of cells for which you want to enable the popup calendar or date picker.
- From the ‘Data’ menu, navigate to ‘Data validation’.
- This will open the ‘Data validation’ window.
- Click on the dropdown arrow next to ‘Criteria:’ and select the ‘Date’ option from the dropdown list.
- For the ‘On invalid data:’ field, select the radio button for ‘Reject input’.
- Click Save.
Note: You can add a popup note to the cell telling the user to double click on the cell to see the calendar.
Now you can see the date picker simply by double clicking the cell(s) to which you had added the Data validation.
In this tutorial we have shown you how to insert a Google Sheets date picker when you have a valid date already entered in a cell.
We also showed you how to use Data validation to ensure that certain cells always display the date picker whenever the user double clicks on the cell.
We hope this simple tutorial was helpful for you.