How to Insert a Date Button in Google Sheets

Dave | December 8, 2022 |

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.

A date cell being edited showing a date picker
Editing a cell containing a valid date

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 EnteredActs as a Date Button?
01 01 2022Yes
Sunday 1 Jan 2023Yes

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:

  1. First, select the range of cells that you want to enforce date entry for
  2. Select Data > Data validation from the menu
  3. Change the Criteria to be Date and is valid date
  4. Optionally: Change the criteria to restrict entry of date to be before, after or between other dates
  5. 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
  6. Optionally: Enter a custom error message to show when your criteria mean the date is invalid
  7. 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.

An empty cell with data validation rules forcing a date picker to show
An empty cell showing a date picker

Here are some screenshots to help you through the process.

The Google Sheets data menu with Data validation highlighted
Data validation
The Data validation window set to restrict entry to be a valid date only
Adding a date only validation rule

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 Google Sheets mobile app showing what happens when you try to edit a date validated cell, with the date picker button highlighted
See the date picker button?

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.

Entering invalid date data in mobile sheets
Invalid dates on mobile sheets

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.


I'm a Google Product Expert and mainly post on the subject of Google Sheets.

I've been a software engineer for over 20 years. The constant through all that time? Spreadheets.... Even though I can write programs I use them.... a lot. Sometimes there's just no better alternative!

Find out more about me here.

Leave a Comment