How to use Data Validation to enter dates

Entering dates into a Google Spreadsheet is straightforward; you can simply type the date out in a cell, but there is a better way; by using data validation. We'll explore how to add data validation to enter dates here.

Once a date has been added manually, if you double-click on the cell again, you’ll notice that a date picker appears, which is useful when you want to adjust it later.

However, there are occasions when you might like to have this date picker appear in all cells in a particular column, even when the cell is blank, to save time and ensure the format of the entered date remains consistent. For example, keeping the date format consistent is particularly important if you need to count the number of days between two dates.

Using Data Validation in Google Sheets, it is possible to make a date picker appear in every cell in a column. Let’s see how this is done.

1. With the range of cells highlighted where you want to include the date pickers, right-click anywhere in the highlighted range to bring up the contextual menu.

2. Choose the option right at the bottom - View more cell actions

3. Then choose Data Validation from the pop-out menu

It’s also possible to access this Data Validation option from the Data menu at the top of the sheet.

Both options will open up the Data Validation window shown below.

4. Click on the Criteria dropdown and choose the Date option

The dropdown field next to this has a range of options specific to the date criteria, but for this example, we’ll leave this second dropdown menu selected as ‘is a valid date’.

5. Lastly, we will click the reject input option to ensure the dates are entered consistently. This will only allow dates to be entered in the cell; anything else will be rejected.

You can leave this set to ‘Show warning’ if you prefer, but I would suggest going for the reject option to be on the safe side.

As an optional extra, you can tick the ‘Appearance’ checkbox to show a helpful tip. You can change this text to whatever you need.

If you or anyone else enters a value that is not a valid date, they will see this warning message appear.

Once you’re done, just click on the Save button. You’ll notice the cell range for this validation is B4 to B50, so anything entered outside this range won’t have this data validation applied. If you need to extend this range, you can adjust it later by following the same steps to bring this window up, but you’ll need to ensure your selected cell is within this original range.

Now, when you double-click any cell from this range B4:B50, you’ll see a date picker appear.

This is great when you know you or anyone else who has access to your sheet will always enter dates in a particular column as it adds structure to your spreadsheet and keeps things clean.

As you know, it’s possible to enter dates in various ways, such as 13/7/2022, 13/07/2022 or 13/07; using this data validation for valid dates will eliminate this random behaviour completely.

Have a go yourself with the Google Sheet document used in this example.