How do I make a date drop down list in Excel?

Put a column of real Date values on a helper sheet, select the cells where you want the drop down, open Data Validation, choose List, and set Source to the date range. Excel reads each date as one allowed choice.

Why is my date drop down empty?

The Source range usually points at blank cells, or the range is too narrow to cover every date you generated. Regenerate the list, widen the range to include every row, and confirm the cells are formatted as Date, not Text.

Can Excel create a date drop down without typing every date?

Yes. Generate the sequence with the Date List Generator, paste it into a helper column, and point Data Validation at that column. This avoids manual entry and keeps the list sorted chronologically.

How do I add weekday names to the drop down?

Add a second helper column with =TEXT(A2,"dddd") that references each date, or apply a custom number format like ddd, mmm d to the target cells so the weekday appears next to the chosen date.

How many dates can I include in one drop down?

Data Validation lists are capped by the size of the Source range you point at. The Date List Generator supports up to 10,000 dates in a single run, which covers daily entries for about 27 years or longer spans when you step by week.

Does the drop down update if I change the source list?

Yes. The drop down always reflects whatever is in the Source range at the moment it is opened. Replace the helper column with new dates, or convert it to an Excel Table and reference the table column so the list grows automatically.

How to Create a Date Drop Down List in Excel

A date drop down list in Excel is a Data Validation control whose Source points at a column of real Date values, so a user clicks a cell, opens the arrow, and picks a calendar date instead of typing one. The list itself is just a vertical column of dates formatted as Date type; Excel's Data Validation menu accepts that range as the list of allowed values. Once the source is in place, every cell that uses that validation shows the same arrow and the same choices, which is what most people mean when they ask how to create date drop down list in Excel.

The catch is that Excel does not generate the date sequence for you. You still need a tidy column of dates covering whatever range your form needs: every weekday for a quarter, every Monday for a sprint, every day of the year for a journal. Typing those by hand is slow and error-prone, and formulas like SEQUENCE work but only on newer Excel builds and only if the user understands array formulas. A faster workflow is to generate the list once with a dedicated tool, paste it into Excel, then wire up Data Validation to that range. That is the gap the Date List Generator fills.

how to create date drop down list in excel

What you actually build in Excel

There are three pieces on the worksheet, and each one has a job.

Source list. A column of dates formatted as Date. This is what appears inside the drop down arrow.
Optional helper column. A second column that displays the weekday name, useful when the drop down should read "Mon, Jan 6" instead of a bare date.
Target cells. The cells where users click and pick a date. Data Validation lives on these cells, not on the source.

The Data Validation dialog itself only cares about the Source field. You point Source at the source list, and Excel reads each cell in that range as one allowed value. There is no "date" toggle inside Data Validation; the dates are recognized because the source cells already hold real Date values, not text.

Generate the date list with the Date List Generator

The Date List Generator produces an inclusive sequence of calendar dates between a chosen start and end, with a day step you control. Up to 10,000 dates fit in one run, which covers roughly 27 years of daily entries or longer ranges when you step by week or month.

Open the Date List Generator and pick a Start date that matches the first day your form should offer.
Pick an End date that is the same as or later than the Start date; the generator stops inclusive, so the End date appears when the step lands on it.
Enter a whole-number Day step such as 1 for every day, 7 for every week, or 14 for biweekly pay periods.
Toggle weekday names on if you want a second column showing Monday, Tuesday, and so on next to each date.
Click Generate, then check whether the final row of the output lines up with your End date. If it stops one step short, your step does not divide the range evenly; that is expected and harmless.
Copy the one-per-line output and paste it into a column on a helper sheet in your workbook. Excel will keep the values as Date because the generator outputs ISO-style dates.

Once pasted, the column is your source list. Rename the sheet to something like Lists or _data so it sits out of the way of the visible form.

Wire the generated list into Data Validation

With a clean date column in place, the rest is standard Excel Data Validation. The steps below assume you pasted dates into cells Lists!A2:A365 for a year of daily entries.

Select every cell where users should pick a date. A single cell, a range like D2:D100, or a whole column header excluded all work.
Open Data, then Data Validation, and choose List from the Allow drop down.
In the Source box, type the range that holds your generated dates, for example =Lists!$A$2:$A$365. Dollar signs lock the range so copy-pasting the validation does not shift it.
Click OK. Each selected cell now shows a drop down arrow. Clicking it opens the full list of dates in chronological order.
If the source sits on a different sheet and Excel refuses the reference, define a name: select the dates, click Formulas, Define Name, and call it DateList. Then set Source to =DateList.

If you generated weekday labels in a second column, you do not need to add them to the drop down itself. Users will see the date in the cell; the weekday is for your reference on the helper sheet, or for a custom number format like ddd, mmm d.

Format the drop down cells so dates sort cleanly

A common complaint is that a date drop down "sorts wrong." The cause is almost always that the source column is stored as Text, not Date. Excel still shows the drop down, but Text dates sort alphabetically instead of chronologically.

Select the source column, open Format Cells, choose Date, and pick a short format like 2026-01-06 or m/d/yyyy.
If pasted values came in as Text, use Data, Text to Columns, Finish to coerce them back into real Dates without changing the visible values.
Apply the same Date format to the target cells so picked values display consistently.

Once both columns share a Date format, the drop down opens in calendar order and any downstream SUMIFS, VLOOKUP, or pivot table built on those cells behaves correctly.

Add weekday labels next to the drop down

Some forms read better when the chosen date is shown alongside its weekday, for example "Mon, Jan 6, 2026." Excel's TEXT function handles this without touching the stored date.

In the cell next to your drop down, enter =TEXT(D2,"ddd, mmm d, yyyy"), where D2 is the validated cell. Drag the formula down to mirror the form. Because TEXT references the actual Date value, not the displayed text, the weekday stays in sync with whatever the user picks.

When the list needs to grow over time

A static list works for fixed forms like a one-quarter schedule. For longer-running sheets, switch the Source from a hard range to a named range that expands, or replace the validation with a dynamic array.

Define a name like DateList that points at the column on the helper sheet using a structured table reference: =Table1[Date]. Adding rows to the table grows the list automatically.
On Microsoft 365, you can use =SEQUENCE(end_date - start_date + 1, 1, start_date, 1) inside a spilled range and point Data Validation at that spilled output.
Re-run the Date List Generator whenever the range needs to extend, then replace the old source column; the drop down picks up the new dates on the next click.

For random sampling and other one-off date needs, the Random Date Generator is a useful companion; it samples uniformly within an inclusive range, which is handy when you want a test cell filled with a believable date.

Troubleshooting common problems

A few patterns repeat when date drop downs misbehave, and each has a quick fix.

Symptom	Likely cause	Fix
Drop down is empty	Source range points at blank cells	Regenerate the list and re-copy, or extend the range to cover every populated row
Dates appear but sort as text	Source stored as Text	Run Text to Columns on the source column to coerce to real Date
Validation error on a different sheet	Direct sheet reference is blocked	Define a named range and reference the name in Source
Drop down shows numbers like 45687	Target cell formatted as Number or General	Apply Date format to the target cells as well
New dates do not appear in arrow	Source range is fixed and too short	Replace Source with a table column reference or extend the range manually

If you also build lists of values for other drop downs, the same Data Validation pattern works for non-date items; the source column is just text instead of dates. That approach scales the same way, so once you have the helper-sheet pattern in place you can reuse it for category lists, status fields, and similar controlled vocabularies.

For teams building schedule sheets, pair the generated date source with the Random Team Generator when you need to assign each date to a person; both tools run locally, so no data leaves the browser.

More on this topic: Generate a List of Random Things to Do in Minutes.

For a deeper look, see How to Get Random Teammates in Arena Breakout Fast.