Where to find date settings in Excel

Excel pulls date formats from your computer's regional settings, but you can override those defaults right inside the spreadsheet. The fastest way is to select the cells containing dates, right-click, and choose Format Cells. This opens a dialog where you control how dates display without touching your system settings.

If you want to change how all dates behave in a single workbook going forward, you can also adjust the default date format through Excel's options menu. The location depends on whether you use Excel on Windows or Mac, and which version you have, but the principle is the same: find the format settings, pick your date style, and apply it.

Key Takeaways

  • Right-click any cell with a date, select Format Cells, and choose the Date tab to change how that date displays without altering the actual data.
  • Excel recognizes dates based on your computer's regional settings, so a date that looks like 03/04/2024 might mean March 4th or April 3rd depending on your location.
  • Custom date formats let you display dates in almost any order or style — for example, "Monday, March 4, 2024" instead of "3/4/24".
  • Changing the format does not change the underlying date value, so formulas and sorting still work correctly.
  • If Excel is not recognizing your entry as a date, it is usually because the format does not match your system's regional date pattern.

Changing the date format for selected cells

Select the cell or range of cells that contain the dates you want to reformat. Click and drag to highlight multiple cells, or click the first cell, hold Shift, and click the last cell in the range. If your dates are scattered across the sheet, hold Ctrl (or Cmd on Mac) and click each cell individually.

Right-click the selection and choose Format Cells from the menu. A dialog box opens. Click the Number tab at the top if it is not already selected, then click Date in the Category list on the left. You will see a list of date formats in the middle column — for example, "3/14/2024", "14-Mar-2024", or "March 14, 2024". Click the format you want, and Excel shows you a preview of how your dates will look. Click OK to apply it.

The date itself does not change — only how it appears on screen. If you had a formula referencing that cell, it still works exactly the same way. Sorting and calculations treat the cell as the same date no matter which format you chose.

Creating a custom date format

If none of the built-in formats match what you need, you can build your own. Open the Format Cells dialog the same way: select your cells, right-click, and choose Format Cells. Go to the Number tab and click Date in the Category list. At the bottom of the dialog, you will see a field labeled Type or Format Code. This shows the code for the currently selected format — something like "mm/dd/yyyy" or "d-mmm-yy".

Click in that field and edit it directly. Here are the most common codes: d is the day (1–31), dd is the day with a leading zero (01–31), m is the month (1–12), mm is the month with a leading zero (01–12), mmm is the three-letter month name (Jan, Feb), mmmm is the full month name (January, February), yy is the two-digit year (24), and yyyy is the four-digit year (2024). You can combine these with slashes, dashes, spaces, or any text you want. For example, "dddd, mmmm d, yyyy" produces "Monday, March 4, 2024".

Type your custom code and click OK. Excel applies it immediately. If the format does not work the way you expected, open Format Cells again and adjust the code.

Changing the default date format for your workbook

If you want every new date in a workbook to use a specific format, you can set a default. On Windows, click the File menu, then Options, then Advanced. Scroll down to the Editing options section. You will not find a direct "default date format" setting here, but you can change your system's regional date format, which Excel will then use. On Mac, click Excel in the menu bar, then Preferences, then View — again, the date format follows your system settings.

The easier approach is to format a few cells the way you want them, then use the Format Painter to copy that format to new cells as you add data. Select a cell with the format you want, click the Format Painter button (it looks like a paintbrush) in the toolbar, then click and drag across the cells you want to format the same way.

Understanding how Excel recognizes dates

Excel decides whether something is a date based on your computer's regional settings. If your system is set to US English, Excel expects dates in the order month/day/year. If it is set to UK English, it expects day/month/year. When you type "03/04/2024" in a US system, Excel reads it as March 4th. In a UK system, the same entry becomes April 3rd.

If you type a date and Excel treats it as text instead of a date, it is usually because the format you typed does not match your system's expected pattern. For example, if your system expects mm/dd/yyyy and you type "2024-03-04", Excel may not recognize it as a date. To fix this, format the cell as a date first, then type the value. Or type the date in a format Excel definitely recognizes — like typing the month name, for example "March 4, 2024".

You can check what your system thinks a date is by looking at the formula bar. If the cell shows "3/4/2024" but the formula bar shows the text "3/4/2024" in quotes, it is stored as text, not a date. Select the cell, go to Format Cells, choose Date, and click OK — this often converts it to a real date that Excel can work with.

Fixing dates that appear as numbers

Sometimes a date displays as a long number like "45351" instead of a readable date. This happens because the cell is formatted as a number instead of a date. The number itself is correct — Excel stores all dates as numbers counting from January 1, 1900 — but you need to change the format to see it as a date.

Select the cell or cells showing the number, right-click, and choose Format Cells. Click the Number tab, then click Date in the Category list. Choose any date format from the list and click OK. The number converts to a readable date immediately. If the date still looks wrong, your data might have been entered in a different date format than your system expects — in that case, you may need to use a formula to rearrange the parts, but formatting alone usually solves the problem.

Handling dates from other programs or sources

When you copy dates from another program — like a PDF, a website, or an email — they sometimes arrive as text instead of dates. Excel will not format them as dates until it recognizes them as dates. Paste the data into Excel, select the cells, and try formatting them as dates using Format Cells. If that does not work, the text is probably in an unusual format that Excel does not recognize.

In that case, you can use a formula to convert it. For example, if the dates are in a column as text like "04-Mar-2024", you can use the DATEVALUE function: type =DATEVALUE(A1) in a new column, where A1 is the cell with the text date. Excel converts it to a real date, which you can then format however you want. Copy the formula down for all rows, then copy the results and paste them back as values to replace the original text.

Frequently Asked Questions

Why does my date look different when I send the file to someone else?

Their computer's regional settings are probably different from yours. If your system is set to US English and theirs is set to German, the same date might display as "3/14/2024" on your screen and "14.3.2024" on theirs. The underlying date is the same — only the display format changed. If you need the date to look the same everywhere, use a custom format with the full month name, like "14 March 2024", which is unambiguous in any language.

Can I change the date format for just one cell without affecting the others?

Yes. Select only that one cell, right-click, choose Format Cells, pick your date format, and click OK. The change applies only to the selected cell. Other cells with dates keep their original format. You can format each cell or group of cells independently.

What if I want dates to show the day of the week as well?

Use a custom format. Open Format Cells, go to the Date category, and edit the format code to include "dddd" for the full day name or "ddd" for the three-letter abbreviation. For example, "dddd, mmmm d, yyyy" displays as "Monday, March 4, 2024". Type your code and click OK.

Does changing the date format change the actual date value in my spreadsheet?

No. Formatting only changes how the date appears on screen. The underlying value stays the same, so formulas, sorting, and calculations all work correctly. You can format the same cell ten different ways and the date itself never changes.

Why is Excel showing my date as a number even after I formatted it as a date?

The column might be too narrow to display the full formatted date. Try widening the column by double-clicking the border between the column headers — Excel auto-fits the width. If that does not work, the data might be text instead of a date, in which case formatting alone will not fix it. Check the formula bar to see if the value appears in quotes.