Google Sheets Calendar Dropdown: How to Add One

To add a calendar picker in Google Sheets, select the cell, choose Format > Number > Date, then double-click the cell; a date picker should appear. For stronger control, use Data > Data validation to require a valid date or limit entries to a date range, then use the picker to enter dates.

What a calendar dropdown in Sheets actually is

People often call the date picker in Google Sheets a calendar dropdown, but it is not quite the same as a dropdown list. A list dropdown displays choices such as Open, In progress, and Done. A date picker is a small calendar interface for choosing a date, usually attached to a cell formatted as a date. The distinction matters: if you want someone to pick any day on a calendar, format the cell as a date; if you want to restrict which dates are permitted, add data validation too.

A date picker helps reduce typing errors and keeps dates in a consistent format. It does not, by itself, guarantee that every entry is sensible for your project. For example, a booking sheet might need dates only within the next six months, while a timesheet might need dates within a specific pay period. Validation rules can enforce those limits; the calendar is simply a convenient way to enter a date.

Add a calendar picker to a cell

In Google Sheets in a web browser, first select the cell or cells where people will enter dates. Apply date formatting, then open the picker by double-clicking a cell. The calendar control lets you choose a day rather than typing the date yourself. The exact interaction can vary slightly with interface changes, but date formatting is the basic step for displaying a date picker in a cell.

Select a cell, such as B2, or select a range such as B2:B50.
Choose Format > Number > Date. Sheets applies a standard date display format.
Double-click a formatted cell to open its date picker.
Choose the intended day in the calendar. The cell receives a date value, displayed using its number format.

Formatting changes how a value appears; it does not necessarily change the underlying date value. For example, the same date can be displayed as 3/7/2026 or Mar 7, 2026 depending on the chosen format and locale. If the display is not what you want, select the cells and use Format > Number to choose another date format. A cell that contains text resembling a date may not behave like a true date, so validation is useful when a sheet will be used by others.

Use data validation to control dates

To make a date field more reliable, add a validation rule. Select the target cells and choose Data > Data validation, then choose Add rule if the side panel offers that option. Under Criteria, choose a date condition, such as Date is valid date, or choose a condition that limits the permitted dates. The available date conditions can include a specific date, dates before or after a date, and dates between two endpoints.

For a simple event-planning sheet, select Date is valid date to reject entries that are not recognized as dates. For a booking form, a between rule can limit entries to the reservation window. Configure the rule’s handling for invalid data: rejecting the input is stricter, while showing a warning allows it but flags the issue. Add help text if collaborators may not know the allowed period. Click Done to save the rule.

Validation and formatting solve different problems. A date format makes dates easier to read and supports the calendar interaction; a validation rule checks whether an entry meets your criteria. If a cell is only formatted as a date, a user may still be able to enter an out-of-range date. If a cell has validation but an awkward number format, the data can be valid while appearing confusing. For a shared date-entry column, use both.

Choose the right date rule for your sheet

Think about what a valid date means in your particular workflow before choosing a rule. A project tracker may accept any real date, whereas a class registration sheet may accept only dates in a term. Avoid imposing a narrow restriction just because it seems tidy: a rule that is too strict can block legitimate corrections or future planning.

Any real date: Choose Date is valid date when the sheet should accept dates without a special time limit.
A fixed deadline: Use a before or after condition when entries must fall on one side of a cutoff.
A permitted window: Use a between condition for start and end dates, such as a defined reporting period.
One exact day: Use an is condition when each entry must match a specific date, such as a scheduled inspection.

Consider whether endpoints should be inclusive and test the rule with a date on each boundary. When dates are used for reporting, also decide which date convention the team expects. Google Sheets stores dates as values, but people may interpret numeric displays differently across locales. A clear format such as 7 Mar 2026 can be less ambiguous than 3/7/2026 in a sheet shared internationally.

Apply the picker to a column or a shared template

For a repeated workflow, format and validate the whole intended range rather than setting up one cell at a time. For example, apply the date format and validation rule to B2:B100 in a task register, leaving B1 for the header. New rows within that range will then follow the same rules. If a sheet is likely to grow beyond the selected range, extend the formatting and validation when adding rows, or set up a generously sized range while avoiding unnecessary restrictions on unrelated cells.

Use descriptive headers such as Due date or Appointment date, and add a note or validation help text if users need to know which dates are allowed. Protecting a sheet or range can prevent accidental changes to formulas and layout, but protection is separate from validation: it does not make the date rule more accurate. Check the sharing permissions as well. A person who can edit the sheet can generally enter data in unprotected cells, but access settings and organizational policies may affect what collaborators can do.

Common problems and how to fix them

No calendar appears: Confirm that the cell is formatted as a date, then double-click it. Also check that you are editing the sheet in a browser and that the cell is not protected or otherwise unavailable for editing.
The entry is rejected: Open Data > Data validation and inspect the rule for that cell. Check the date range, boundary dates, and whether the rule rejects invalid input. A date outside the permitted period can be a legitimate date but still fail the rule.
The date displays as a number: The value may still be a valid date serial, but the cell may use a number format rather than a date format. Select it and choose Format > Number > Date.
A typed date is interpreted incorrectly: Date interpretation can depend on spreadsheet locale. Check File > Settings for the spreadsheet’s locale, and use an unambiguous date display when collaborators work in different regions.
Some cells behave differently: The cells may not share the same formatting or validation rule. Select a problem cell and inspect its number format and the Data validation panel rather than assuming the whole column is configured uniformly.

One common misconception is that a calendar picker automatically restricts users to valid business dates. It does not know that weekends, holidays, or dates outside a project period are inappropriate unless you build rules or other sheet logic for those requirements. Even a validation rule does not create a complete scheduling system: it checks the entered value against its criteria, not every operational consequence of selecting that date.

Calendar picker versus a dropdown list

Use the calendar picker when the user should choose from dates across a range. Use a dropdown list when the permitted choices are a short, fixed set, such as Morning, Afternoon, and Evening. A dropdown containing every date in a year is possible in principle if you create a list of dates, but it is cumbersome to scroll and maintain; the calendar is usually more practical for that case.

To create an ordinary dropdown, select the cells and use Data > Data validation, then set criteria to a dropdown or a dropdown sourced from a range, depending on the choices you need. That control is distinct from date formatting and date validation. If your goal is to make a cell look like a calendar field, do not create a long dropdown list of dates unless there is a specific reason to present only those options.

Google Sheets, Excel, and other ways to enter dates

These instructions are for Google Sheets in a web browser. Excel has date formatting and data validation, but its standard desktop date-entry experience is not identical to the Google Sheets cell calendar picker. Do not assume that the same double-click behavior or menu labels apply in Excel. If a workbook will move between the two products, test the date formatting and validation after conversion; spreadsheet features and interface behavior can differ.

For dates that follow a predictable pattern, typing or pasting dates can be faster than selecting each date from a calendar. You can also copy a validated, correctly formatted cell to extend its setup, then verify that the validation range covers the new entries. For a form intended for many respondents, a Google Form date question may provide a simpler collection experience, with responses stored in a linked spreadsheet. That approach is useful when respondents should submit information without editing the spreadsheet layout directly.

Frequently Asked Questions

How do I make a calendar pop up in a Google Sheets cell? Format the cell with Format > Number > Date, then double-click it. If the picker does not appear, confirm the cell is editable and formatted as a date.

Can I restrict the calendar to dates between two days? Yes. Select the cells, open Data > Data validation, add a rule, and choose a date condition such as a between rule. Enter the permitted boundaries and choose whether invalid input is rejected or only flagged.

Can I add a date picker to an entire column? Yes. Select the entry range in the column, apply a date number format, and add a date validation rule to that same range. Leave the header cell out of the selection.

Why does my date show as a number in Google Sheets? The cell may contain a date value but be formatted as a number. Select it and choose Format > Number > Date, or choose a different date display format from that menu.

Is a calendar picker the same as a dropdown? No. A calendar picker helps select a date, while a dropdown presents a defined list of choices. Use date formatting and date validation for date entry, and a dropdown rule for fixed labels or options.

Can collaborators use the calendar picker on a phone? Google Sheets is also available as a mobile app, but the interface and date-entry controls can differ from the browser steps described here. For a shared workflow, test the sheet on the devices collaborators will use and make sure the validation rule still communicates the allowed dates.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.