Calendar dropdown or date picker is one of the most popular features of Google Sheets. To enable the date picker in a cell, you need to go to the “Data” tab of the main menu and select the “Data validation” feature.
It displays a calendar popup from which you can select the date. This date is entered into the cell automatically in one of the predefined date formats by Google Sheets.
It also means that you won’t face any errors in future while analyzing and organizing datasets by dates after using a date picker.
In this quick guide, we will discuss inserting a calendar dropdown in Google Sheets. Make sure to read the article till the end so that you won’t miss any steps.
How does the Google Sheets Calendar or Date Dropdown Appear?
A date picker will look like the one below in Google Sheets.
It displays a monthly calendar from where you can pick up a date. The arrows at the top right side of the popup allow you to navigate to the next month.
Note that the start date of the week is Sunday.
Now, without any further ado, let’s learn to insert a date picker into a cell in Google Sheets.
How to add Calendar or Date Dropdown in Google Sheets
There are two ways in which you can add a date picker to a cell. Both of them are pretty straightforward.
The key is to set the cell format to Date. Without proper formatting of the cells, the Calendar Dropdown won’t work.
Here are the two methods to insert calendar or date dropdown.
METHOD #1 – Using the Data Validation feature in Google Sheets
This method is useful when you have multiple cells for which you wish to display the date picker popup.
Here are the steps to insert the Calendar or Date dropdown for multiple cells.
- Open a new Google Sheet by clicking here
- Select all the cells where you wish to insert the date picker
- Hover to the main menu and click on “Data” tab
- Select the “Data validation” from the popup
- A new dialog box will be displayed on the right side of the screen
- Click on the “Add rule” button
- Click on the dropdown below the “Criteria” option
- Choose “Is valid date”
- Click on the “Done” button in the green at the bottom
- Now, click on any cell you selected in step 2
- A calendar (date picker) will popup
- Select any desired date
As you can see in the above GIF, once you select a date using the date picker, it will follow the United States date formatting as month, day, and year (mm-dd-yyyy).
Q. How do you change the default date format in Google Sheets?
Most of the countries follow the United States date format, but if your country is the exception, then follow the steps below.
For the sake of this article, we will change the default date format to the United Kingdom. It is day, month, and year (dd-mm-yyyy).
Here are the steps,
- Hover to the main menu and click on “File” button
- Select the “Settings” option from the popup
- A new dialog box will open, as shown below,
- Click on the dropdown below the “Locale” to explore the list of available countries
- Choose the “United Kingdom”
- Click on the “Save and reload” button
The Google Sheets will instantly reload the current page, and the dates will be formatted to the United States.
Q. How to use the Custom Date Format in Google Sheets?
Apart from the predefined date formats that are specific to certain countries, you can select from a range of custom date formats in Google Sheets.
Follow the steps below,
- Select all the cells containing the date
- Hover to the main menu and click on the “Format” tab
- Select the “Number” option from the popup
- Next, select the “Custom date and time”
- A new dialog box will appear
- Select the date format from the list of available options
- Click on the “Apply” button at the top
Note that you can create your own date format using the top section of the Custom date and time formats dialog box, as shown below.
Click on the month, date, and year to see the available formatting options. Once you are done, press the “Apply” button at the top.
Q. How do you allow users to select a Date from a Date Range using the Date Picker in Google Sheets?
Here, as well, we will use the same Data validation feature by Google Sheets, and instead of choosing the “Is valid date”, we will choose the “Date is between” option.
Let’s begin,
- Open a new Google Sheet by clicking here
- Select all the cells where you wish to insert the date picker
- Click on the “Data” tab from the main menu
- Click on the “Data validation” feature from the popup
- A new dialog box will be displayed on the right side of the screen
- Click on the “Add rule” button
- Click on the dropdown below the “Criteria” option
- Choose “Date is between”
- Two new empty boxes will appear, as shown below
- Enter the start date in the first box. For example, we will put “1/1/2023”
- Enter the end date in the second box as “1/31/2023”
- Click on the “Done” button in the green at the bottom
Now, if you click on any cell from step 2, you will see a date picker. Note that if you select any date that is after or before the selected date range, the Google Sheets will display the following error.
The error asks the user to put a date between the defined date range.
METHOD #2 – Manually Entering a Valid Date
This is the quickest method of adding the date picker to a cell. It’s the best choice when you are working with a single or less number of dates.
- Open a new Google Sheet by clicking here
- Click on any cell. We will click on the cell “B2”
- Type the date “1/1/2023”
- Press the “Enter” key
Now, double-click on the cell “B2” to see the date picker
This process will work only in case of valid dates.
To ensure valid dates, you need complete knowledge of date formats in Google Sheets.
Consider the following example,
In the above image, the first three cells contain valid dates, and the remaining two cells contain invalid dates.
How to check a valid date in Google Sheets
Dates can be written in various formats, and as human beings, we can interpret them. However, in the case of Google Sheets, it only accepts dates written in specific format.
Let’s discuss a few methods to validate a date in Google Sheets.
1. Check the alignment of the date
As shown in the above image, the valid dates are aligned to the right side of the respective cell.
Sidenote: If you are unable to see the alignment of the date, it may be mainly due to the width of the cell. Make sure to adjust the width of the cell correctly.
Google Sheets will instantly shift the entered date to left or right based on its format. So, this is the fastest method of data validation in Google Sheets.
2. Use the ISDATE function
The ISDATE function by Google Sheets returns TRUE for valid dates and FALSE for invalid dates.
This simple formula has the following syntax,
=ISDATE(value)Here, the “value” argument needs to be replaced with the date you wish to check.
You can either manually enter the date or provide the cell reference.
Let’s use the ISDATE function on the above dates. Here is the output.
The function has returned TRUE for the first three dates and FALSE for the fourth and fifth dates of the table.
3. Use the DATEVALUE function
All dates in spreadsheet software like Google Sheets are treated as numbers.
The DATEVALUE function in Google Sheets is used to return an integer number for a valid date.
It is a simple formula that has the general syntax as below,
=DATEVALUE(date_string)
In the above formula, the “date_string” argument needs to be replaced with the date you wish to validate. Note that you can manually enter the date or select the cell reference containing the date.
Let us use the DATEVALUE function on the above dates. Here is the output.
As the first three dates are valid, the DATEVALUE function has generated integer numbers for them. Whereas, for the remaining dates from the fourth and fifth cells, the function has returned the “#VALUE!” error.
Final Words
Date picker is really useful when you wish to share the Google Sheet with clients or colleagues and want them to fill in the dates. They can simply click on the desired cell and pick a date from a calendar.
Make sure to use the Data Validation feature correctly. We also learned to use this feature to allow users to select a date from a predefined date range.
Note that it is not possible to change the appearance of the date picker popup as of August 2023.
I hope this article taught you all the bells and whistles when it comes to inserting and using a date picker in Google Sheets. In case you are stuck somewhere or facing any errors, feel free to comment below.