How to add a Date Picker in Google Sheets | Easy 2 min Guide

How to add a Date Picker in Google Sheets | Easy 2 min Guide
Reading Time: 4 minutes

In this article we will see how to implement a date picker in Google Sheets to enter dates and also how to validate the date entered. 

Date Picker in Google Sheets

Need for Date Picker

If there are many users accessing a sheet, there is always a possibility that someone would enter the date in incorrect format which can mess up the formulas and functionality of the sheet. Hence a date picker is very useful in such scenarios. It is pretty easy to implement one and we will see how to implement a date picker in Google Sheets in this article.

Implicitly insert a date picker in Google Sheets

If you insert a date in a valid format in any cell, then, by default google sheets shows the date picker when you double click on the cell.

Implicitly enter a date picker in Google Sheets
Figure 1: Implicitly enter Date Picker in Google Sheets

Alternatively, to be sure, after you enter the date in any cell, you can explicitly convert the cell to choose a date picker. To do so select the cell, then go to Format>Number>Date.

Explicitly set date picker in Google Sheets
Figure 2: Explicitly set Date Picker in Google Sheets

So now, whenever you double click on the cell, a date picker would pop up.

Add a Date Picker to a cell

What we saw till now was that we entered a date into a cell and sheets automatically showed a date picker when we edited the value. What if we needed to insert a date picker into an empty cell.

To do the given we can make use of the Data validation Feature in Google Sheets

  • Click and select the cells or range of cells you want to insert the date picker in
  • Go to Data>Data Validation
Data validation option in Google Sheets
Figure 3: Data Validation option in Google Sheets
  • This would open the data validation popup. This contains a lot of options that we would go through later.
Data validation options
Figure 4: Data Validation options
  • In the criteria section, select “Date” from the dropdown. The system would choose “is a valid date” by default. If not, then choose from the next dropdown
  • Select the radio button for “Reject Input” so that the sheet does not accepts invalid date formats.
  • Additionally, you can add a message for the user. To do that check the appearance checkbox and add the relevant message in the text input.
  • The final settings would look like this. After this, hit “Save”.
Data validation option for a date picker
Figure 5: Data Validation options for a date picker
  • Now if you double click on the empty cell, a date picker will appear.
Sample date picker in Google Sheets
Figure 6: Sample Date Picker in Google Sheets

Set range for date picker

Sometimes you want to have a range of valid dates. To do so go back to the data validation window. Then in the criteria section, choose the rule you want to apply.

Set range for date picker in Google Sheets
Figure 7: Set range for date picker in Google Sheets

Enter the required message as well. Enter the favorable settings and then hit “Save”. After this only valid dates would be allowed in the cell.

Conclusion

We saw how to insert a datepicker in Google Sheets implicitly and explicitly. We also saw how to validate the date so that only the date which satisfies a given condition is entered. You can read more about data validation in here.

See Also

You can checkout other equally good articles on Google Sheets here.

Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.