The simplest way to let someone pick a date in Excel
The fastest method is to use Excel's built-in date picker, which appears as a small calendar icon when you click on a cell formatted for dates. This works in Excel 2016 and later on Windows, and in Excel 2019 and later on Mac. When someone clicks the calendar icon, a popup shows the current month with clickable dates — they pick one, and it fills the cell automatically.
If your version of Excel does not have the date picker, or if you need more control over how dates are entered, you can format cells to accept dates and add data validation rules that guide users toward the correct format. Both methods prevent the common problem of dates being entered as text instead of actual date values, which breaks sorting and calculations.
Key Takeaways
- Excel's date picker appears as a calendar icon when you click a cell, and works automatically in Excel 2016 and later on Windows or Excel 2019 and later on Mac.
- Formatting cells as dates and adding data validation rules ensures dates are stored correctly even if the date picker is not available in your version.
- A dropdown list of preset dates is useful when you want to limit choices to specific dates rather than allowing any date to be entered.
- The date picker works best when cells are already formatted as dates before users start entering data.
Enabling the automatic date picker
Open your spreadsheet and click on the cell where you want the date picker to appear. On the ribbon at the top, find the Home tab. In the Number group, click the small arrow next to the number format box (it shows "General" by default). A dropdown menu opens. Scroll down and select Short Date or Long Date — either one formats the cell to accept dates.
Once the cell is formatted as a date, click inside it again. A small calendar icon should appear on the right side of the cell (on Windows) or when you click the cell (on Mac). Click that icon and a calendar popup appears showing the current month. Click the date you want, and it fills the cell automatically in the format you chose. The date picker remembers which month you last used, so if you are entering dates from the same month, the calendar stays there until you navigate away.
If the calendar icon does not appear, your version of Excel may not include this feature. Move to the next section to set up date entry using data validation instead.
Using data validation to guide date entry
Data validation creates rules that tell Excel what kind of information belongs in a cell. For dates, it can show an error message if someone types text instead of a date, or it can show a helpful message when they click the cell. This method works in all versions of Excel.
Select the cell or range of cells where dates will be entered. On the Data tab in the ribbon, click Data Validation (on Mac, it is under Data > Validity). A dialog box opens. In the dropdown that says Allow, select Date. Below that, you can set a range — for example, between a start date and an end date, or greater than or equal to today's date. Enter the dates you want to allow.
Click the Input Message tab. Type a title like "Enter a date" and a message like "Please enter the date in MM/DD/YYYY format." This message appears when someone clicks the cell, reminding them what to do. Click OK. Now when someone clicks that cell, they see your message, and if they type something that is not a date, Excel shows an error.
Creating a dropdown list of specific dates
If you want users to choose from a set list of dates rather than type any date, use a dropdown. This is useful for scheduling, when you have only certain dates available. First, create your list of dates in a separate area of the spreadsheet — for example, in column E, rows 1 through 5, type the dates you want to offer.
Select the cell where you want the dropdown to appear. Go to the Data tab and click Data Validation. In the Allow dropdown, select List. In the Source field, type the range where your dates are — for example, $E$1:$E$5. The dollar signs lock the range so it does not change if the formula is copied. Click OK.
Now when someone clicks that cell, a dropdown arrow appears. They click it to see the list of dates and select one. The date fills the cell. If you want to hide the list of dates from view, put it on a different sheet in the same workbook, and reference that sheet in the Source field — for example, DateOptions.$E$1:$E$5.
Formatting dates so they display correctly
Excel stores dates as numbers behind the scenes, so how they appear depends on the format you choose. After you have entered dates, you can change how they look without changing the actual data. Select the cells with dates. Right-click and choose Format Cells, or go to Home > Number and click the arrow to open the format menu.
The Date category shows different formats: "3/14/2024", "March 14, 2024", "14-Mar-24", and others. Pick the one that matches how your workplace or organization writes dates. The format you choose affects only how the date appears on screen and in print — it does not change the underlying date value, so sorting and calculations still work correctly.
If you create a dropdown list of dates, format those dates the same way you format the cells where they will be selected. This prevents confusion when someone sees "3/14/2024" in the dropdown but the cell displays "March 14, 2024".
Troubleshooting dates that look wrong
If a date appears as a number like "45000" instead of a date, the cell is formatted as a number instead of a date. Select the cell, right-click, choose Format Cells, select Date from the category list, and click OK. The number converts to a date.
If someone types a date and Excel treats it as text (it appears left-aligned instead of right-aligned), the cell was not formatted as a date before they entered it. Format the cell as a date now, then delete and re-enter the date. Alternatively, if the date is already in the cell, you can use the DATEVALUE function to convert it: type =DATEVALUE(A1) in a new cell, where A1 is the cell with the text date, and it converts to a real date.
If the date picker calendar does not appear, check your Excel version. The feature requires Excel 2016 or later on Windows, or Excel 2019 or later on Mac. If you have an older version, use data validation instead.
Frequently Asked Questions
Can I make the date picker appear without formatting the cell first?
No. The date picker only appears in cells that are already formatted as dates. Format the cell as a date first, then click inside it to see the calendar icon. If you skip the formatting step, Excel treats what you type as text, and the picker does not set up.
What if I want to limit dates to a specific range, like only dates in 2024?
Use data validation. Select the cell, go to Data > Data Validation, set Allow to Date, choose between, and enter your start date (January 1, 2024) and end date (December 31, 2024). Anyone who tries to enter a date outside that range sees an error message.
Can I use a date picker in a shared spreadsheet on OneDrive or SharePoint?
The date picker works in Excel Online, but it looks slightly different — it appears as a text field with a calendar icon. Click the icon to open the calendar. The same data validation rules work in Excel Online, so if you set up validation on your desktop version, it carries over to the online version.
How do I copy a date picker to other cells?
Select the cell with the date picker. Copy it (Ctrl+C or Cmd+C). Select the range where you want the picker to appear and paste (Ctrl+V or Cmd+V). The formatting and any data validation rules copy along with it. If you used a dropdown list, make sure the source range is set up before you copy.
What happens if someone pastes a date from another program into a cell with a date picker?
If the pasted date is in a recognized date format, Excel converts it to a date and the cell displays it correctly. If it pastes as text, format the cell as a date and re-enter it, or use the DATEVALUE function to convert it. Data validation rules still explore after pasting, so if the date falls outside your allowed range, an error message appears.