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. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “excel date picker”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the outcome you expected before you touch the real workbook. 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. The point is a workflow that saves repeating the same clicks every week, but the practical win is that someone else can open the file and understand what happened without asking you.
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. If this is something you do often, add the command to the Quick Access Toolbar (right-click it on the ribbon and choose Add to Quick Access Toolbar) or learn its shortcut, so next time it is one click instead of a hunt through the menus.
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. Here is the takeaway for “excel date picker”: follow the steps at the top, check the result on the sheet itself, and keep undo in mind while you experiment. Done once deliberately, it becomes something you do from memory in Excel rather than something you search for again.
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.
Other ways people ask this
People reach this page typing “date picker for excel” and “adding date picker in excel”, among other phrasings; whichever wording you used, the fix above is the one you want.
Why do people search for this in so many different ways?
Because the same task has many names. “date picker for excel”, “adding date picker in excel” all point at the one operation explained on this page, which is why they all lead here.