The Date Picker option in Google Sheets is useful when you need a quick way for a user to insert a specified date into the worksheet.
The Date Picker works as a data validation option that displays a miniature calendar when the cell is selected. It guarantees that the user inputs a valid date.
Table of Contents
Let’s explore a quick use case.
Say you want to set up a Google Sheet that would require user-input from various individuals. One of the data points you need is the user’s date of birth.
As anyone who has conducted an online survey would know, user input is prone to mistakes and inconsistencies. People write their dates differently, and sometimes it’s ambiguous. Let’s say someone writes the date “07/09/03”. Was this person born on July 9th or September 7th? Are we even sure that “03” refers to a year?
Data Validation is the solution to this issue. When adding Date data validation, Google Sheets utilizes a date picker. Now, the users of your spreadsheet just need to select a particular date from the miniature calendar pop-up. This guarantees that the date they select is a valid date.
Let’s learn how to add a date picker ourselves in Google Sheets and later see it used in a real-life example.
A Real Example of Using the Date Picker
Take a look at the example below to see how we can use a date picker in Google Sheets.
In the picture below, you may notice that our Date column is widely inconsistent and in some cases, unusable. There are dates without years, ambiguous dates, and dates that are simply invalid.
In the revised spreadsheet below, we can add data validation to our Date column to avoid invalid dates. Users simply select the cell and navigate the date picker to find the exact date to input.
Here’s another example of the date picker in action. In the image below, we have a table that uses the FILTER function to get lectures from the previous example.
We filter these items using date ranges. For this spreadsheet to work properly, cells B1 and B2 require data validation. This is to ensure that our FILTER function uses valid dates. The date picker is not only convenient for input, but it is essential for worksheets that only work when valid dates are provided.
Try out the date picker yourself! You can make a copy of the spreadsheet above using the link below:
Easy isn’t it? Let’s move on to a step-by-step guide on how to add a Date Picker in Google Sheets yourself.
How to Add a Date Picker in Google Sheets
- First, we must select a cell or cell range which we want to use the Date Picker for. In this example, we’re selecting the cell range A2:A7
- You can then right-click on your selection and select the Data Validation option to access the Data Validation menu.
- Alternatively, you can find the Data Validation menu under the Data drop-down menu, as seen below.
- Make sure the cell range is filled up correctly. For the criteria, the first option should be set to ‘Date‘ and the second option should be “is valid date”
- Once all the details are filled up correctly, we can apply the validation by clicking on the Save button.
- Now you can select any cell from the given range and see that a date picker appears!
Frequently Asked Questions (FAQ)
- Even after setting up the date picker validation, why am I still able to input incorrect dates?
There are two ways cells with data validation handle invalid data. By default, the cell shows a warning which appears as a red marker on the top-right corner of the cell.
If you want to prevent any kind of invalid data from being accepted, you can change the option in the Data Validation pop-up. You just need to select the ‘Reject input’ option on the ‘On invalid data: ’ setting.
- How do I set up my date picker so that only dates in a certain range are valid?
There are plenty of options for you when it comes to validating dates in Google Sheets. We can add stricter rules to our date data validation if necessary.
In the Date validation settings of the cell, we can edit the ‘Criteria’ field to fit our needs. In the example below, we’re limiting the valid dates to the month of December.
If you were to enter a date outside of this range, Google Sheets will treat the date as invalid.
You now have everything you need to start adding a Date Picker in Google Sheets. Hopefully, this step-by-step guide will help your spreadsheets handle dates more effectively!
Use the Date Picker in Google Sheets along with the various other Google Sheets formulas available to create better spreadsheets. Don’t miss out on more helpful articles like this by subscribing to our newsletter!