In Excel: for a dropdown of week-ending dates without ActiveX, create real dates in a helper range and use Data ▸ Data Validation ▸ List; choose Date validation instead when users may type any date within a range.
On this page7
Decide whether users need a fixed list of week endings or any date between two limits. A list shows choices; a Date rule validates typing.
For the list example, enter =DATE(2026,1,2) in F2, =F2+7 in F3, and fill down to F5. Format those cells as yyyy-mm-dd and check that the interval is seven days.
Select B2:B20, open Data ▸ Data Validation, choose Allow: List and set Source to =$F$2:$F$5. Check In-cell dropdown. Format B2:B20 as dates too.
Enable an Error Alert with Style: Stop and explain the allowed period in the Input Message. Warning and Information alerts allow users to accept an invalid typed entry.
Choose a date and check =ISNUMBER(B2). Then type a date outside the list to check the Stop alert. Delete the test entry after checking.
Check existing or pasted entries separately: copying or filling can bypass validation messages. Desktop Excel provides Data ▸ Data Validation ▸ Circle Invalid Data; do not assume that command exists in the web app.
What this does
Date validation, a date-list dropdown and a calendar control solve different tasks. Allow: Date restricts typed entries between your start and end dates but does not add a calendar popup. Allow: List lets users choose dates you have already generated, such as each Friday in a reporting period. Use numeric Excel dates in the source range so the selected value can feed date arithmetic. A number format changes the display, not text into a date. Legacy ActiveX calendars depend on installed controls and Windows desktop Excel; Mac and web Excel do not support ActiveX, and Microsoft 365 and Office 2024 have ActiveX disabled by default. A missing control is not evidence that enabling the Developer tab will install it. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. Treat “date selector in excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a workflow that saves repeating the same clicks every week into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
For four Friday week endings, put =DATE(2026,1,2) in F2 and =F2+7 in F3, then fill F3 down to F5. Format F2:F5 as yyyy-mm-dd: the dates are 2026-01-02, 2026-01-09, 2026-01-16 and 2026-01-23. Select B2:B20 and set Allow: List, Source: =$F$2:$F$5, with In-cell dropdown checked. Choosing 2026-01-16 in B2 stores a date; =ISNUMBER(B2) returns TRUE and =B2+7 returns the next Friday when formatted as a date. For unrestricted dates during 2026, use Allow: Date, between =DATE(2026,1,1) and =DATE(2026,12,31), rather than the four-item list. A fixed week-ending list prevents users from choosing a different reporting day when typing directly, while preserving numeric dates for calculations. The selected period and the underlying value are both observable; a calendar-looking control alone does not establish either. A practical tip: try it on a copy of the sheet or a handful of sample rows first and check the result before you apply it to the real data. That one habit catches almost every surprise while it is still cheap to reverse.
In Google Sheets
This is a command rather than a formula, so there is no syntax to carry over. Google Sheets arranges its commands in menus across the top instead of a ribbon, names some of them differently and does not have every Excel command — look for the equivalent in the Sheets menus or its help before assuming the steps match. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. If you take one thing from this page on “date selector in excel”, make it the order of checks rather than the individual clicks: confirm what is selected, apply the step, and look at the result before moving on. That small routine is what keeps Excel work predictable when the same task comes back in a slightly different workbook.
Common mistakes
- Expecting Allow: Date to create a pop-up calendar or dropdown arrow; use Allow: List for a finite set of date choices.
- Using an unverified text date list or assuming yyyy-mm-dd formatting converts text. Check the source cells with
ISNUMBERand generate dates with DATE when the input locale is uncertain. - Leaving the Error Alert as Warning or Information when an invalid typed date must be blocked. Select Stop and test the actual rule.
- Assuming validation cleans old entries or prevents invalid data pasted into the range. Audit those entries separately and confirm the rule still exists after a paste.
- Sending a legacy ActiveX calendar to Mac or web users, or assuming a 32-bit installation guarantees the calendar control is installed.
Frequently asked questions
How do I add a week-ending date picker without ActiveX?
Generate the permitted week endings in cells with DATE and seven-day increments. Use that cell range as the source of a Data Validation List. This gives a date-choice dropdown, not a calendar grid, and requires no ActiveX control or VBA macro.
Why do I not see Microsoft Date and Time Picker in More Controls?
More Controls lists ActiveX components installed and registered on that Windows computer; it is not a list of built-in worksheet functions. A particular calendar component may be absent or incompatible. Microsoft 365 and Office 2024 disable ActiveX by default, and Mac and web versions do not support it. Use the list workflow when the workbook must avoid that dependency.
Can I restrict typed dates without a dropdown?
Yes. Choose Allow: Date, set the date interval, enable a Stop error alert and give an input message. It checks direct typing; copying or filling and existing data require a separate check.
Will a date chosen from the list work in a formula?
Use real numeric dates in the helper range and verify the selected value with ISNUMBER. TRUE means Excel stored a number; apply a date format for display. A text label that looks like a date is not enough to establish a usable date value.