Google Sheets are pretty smart. If you enter a valid date into a cell, the next time you try to edit that cell by double clicking it or hitting enter with it selected, a date picker will appear. You can pick a date, and the cell will update.
But what about empty cells that need to accept only dates? To get these to act like a date button, you need to add a validation rule to them.
NOTE: Changing the format of a cell to ‘Date‘ using ‘Format > Number > Date‘ from the menu options will not cause cells to display date picker controls. You need to add a validation rule.
Examples of cell values that act as a date button
If Sheets understands what you enter as a date, then it will show a date control for that cell when you edit it. Here are some examples of valid and invalid date formats.
| Cell Value Entered | Acts as a Date Button? |
|---|---|
| 01/01/2022 | Yes |
| 01 01 2022 | Yes |
| 01-01-2022 | Yes |
| 1/1/22 | Yes |
| Sunday 1 Jan 2023 | Yes |
| 01.01.2022 | No |
Restricting cells to only accept dates
Automatic date pickers are great, but you’re still able to type into the cell and change it to something that ISN’T a date. If you want to restrict your user input, and show date picker controls for cells that are empty, you need to add a validation rule to those cells.
Here’s how to do that:
- First, select the range of cells that you want to enforce date entry for
- Select Data > Data validation from the menu
- Change the Criteria to be Date and is valid date
- Optionally: Change the criteria to restrict entry of date to be before, after or between other dates
- Optionally: Select what to do when a user types invalid data in the cell and does not enter a date, either warn them or reject the input
- Optionally: Enter a custom error message to show when your criteria mean the date is invalid
- Click Save
Now, every time you try to edit a cell within this range, even if it is empty, it will act like a date button and show a date picker control.
Here are some screenshots to help you through the process.
Date validation and buttons on mobile sheets
If you’re making a sheet that you need to open on your phone or tablet using Android or the Google Sheets app, things don’t quite work in the same way. The cells in Column C below have a validation rule that restricts valid data to date only. When we click on one of these cells on Android, we get the option to open a date picker, and by default, it presents us with the numeric keypad, rather than the QWERTY one.
The validation rules still work. If you selected to warn the user about invalid dates, then they’ll see a message like the one below.
So you almost get the same effect as on a desktop, you just have one more click to get to a date control to open.