Where to find date settings in Excel

Date settings in Excel live in two places depending on what you want to change. If you need to alter how dates display — the order of month, day, and year, or whether you see the full year or just two digits — you change the cell format. If you need to change how Excel interprets dates when you type them, or how it calculates dates in formulas, you adjust your system's regional settings, which Excel reads automatically.

Most of the time, you'll work with cell formatting, which takes about 30 seconds. System settings changes are rarer and affect all programs on your computer, not just Excel.

Key Takeaways

  • To change how a date looks in a cell, right-click the cell, select Format Cells, choose the Date category, and pick a format from the list.
  • Excel reads your computer's regional settings to decide whether 03/04/2024 means March 4th or April 3rd, so changing that setting affects all your programs.
  • Custom date formats let you display dates in ways the built-in options don't offer, using codes like YYYY for four-digit year and MMM for three-letter month.
  • If Excel won't recognize a date you type, check that your system's date format matches what you're entering.

Changing how dates display using cell format

Select the cell or range of cells holding the dates you want to reformat. Right-click and choose Format Cells, or press Ctrl+1 on Windows or Command+1 on Mac. The Format Cells dialog opens.

Click the Number tab if it's not already selected. On the left side, click Date from the category list. The middle panel shows all available date formats — scroll through and click the one you want. The preview panel on the right shows how your dates will look. Once you find the format you need, click OK.

The dates in those cells now display in the new format, but the actual values haven't changed. If you copy the dates elsewhere or use them in a formula, they still hold the same underlying date value.

Creating a custom date format

If none of the built-in formats match what you need, you can build your own. Open Format Cells the same way (right-click, Format Cells, or Ctrl+1), go to the Number tab, and click Date in the category list. At the bottom of the dialog, you'll see a field labeled Type that shows the code for the currently selected format.

Click in that field and edit the code, or clear it and type a new one. Common codes are: YYYY for a four-digit year, YY for two digits, MM for month as a number, MMM for a three-letter month name (Jan, Feb), MMMM for the full month name, DD for day, and DDDD for the full day name (Monday, Tuesday). Separate them with slashes, hyphens, or spaces.

For example, typing MMMM DD, YYYY produces "January 15, 2024". Type DD/MMM/YY to get "15/Jan/24". Click OK when your format looks right in the preview.

Understanding how Excel reads dates from your system

When you type a date into a cell, Excel uses your computer's regional settings to figure out what you mean. If your system is set to US English, typing 03/04/2024 means March 4th. If it's set to UK English, the same entry means April 3rd. Excel doesn't ask — it just follows what your operating system says.

You can check your system's date format on Windows by opening Settings, going to Time & Language, then Region, and looking at the date format shown under "Date format". On Mac, open System Settings, click General, then Date & Time, and you'll see the format there. If you need to change it, you can do so in those same settings, but remember that the change affects every program on your computer, not just Excel.

Fixing dates that Excel won't recognize

Sometimes you'll type what looks like a date and Excel treats it as text instead. This usually happens when the format you're typing doesn't match what your system expects. For example, if your system is set to US format (MM/DD/YYYY) and you type "15/01/2024", Excel sees the 15 in the first position and doesn't recognize it as a valid month, so it stores the entry as text.

The quickest fix is to type the date in the format your system uses. If you need to enter dates in a different format regularly, change your system's regional settings to match. Alternatively, you can use the DATE function to build dates from parts: typing =DATE(2024,1,15) always produces January 15, 2024, regardless of your system settings.

Changing the date format for an entire column

Click the column header to select the whole column, then right-click and choose Format Cells. Go to the Number tab, select Date, and pick your format. Every cell in that column will now display dates in that format.

If you want to explore the same format to multiple non-adjacent columns, click the first column header, then hold Ctrl (or Command on Mac) and click the other column headers. Right-click and format them all at once.

Using date formats in formulas and calculations

Changing how a date displays doesn't change its value in formulas. If a cell contains January 15, 2024, and you format it to show only the month and year, it still holds the full date. Formulas that calculate days between dates or extract the year will work the same way.

If you need to display a date in a specific format within a formula result, use the TEXT function. For example, =TEXT(TODAY(),"MMMM DD, YYYY") returns today's date in the format "January 15, 2024". The codes you use in TEXT are the same custom format codes you'd use in Format Cells.

Frequently Asked Questions

Why does the same date look different on my coworker's computer?

Your coworker's system regional settings are probably different from yours. Excel reads those settings to decide how to display dates. If you both open the same file, the dates will display according to each person's system settings unless you've applied a specific cell format that overrides it.

Can I change the date format for just one cell?

Yes. Click the single cell, right-click, choose Format Cells, go to the Number tab, select Date, and pick your format. That cell will display in the new format while the rest of the column stays unchanged.

What does the asterisk next to a date format mean?

An asterisk means that format is linked to your system's regional settings. If you change your system date format later, any cells using that format will update automatically. Formats without an asterisk are fixed and won't change if your system settings change.

How do I show dates with leading zeros?

Use a custom format code with two D's for the day and two M's for the month. For example, MM/DD/YYYY displays January 5, 2024 as "01/05/2024" instead of "1/5/2024". Most built-in date formats already include leading zeros, so check those first before building a custom one.

Can I format dates to show only the day of the week?

Yes. Open Format Cells, go to the Number tab, select Date, and look for a format that shows only the day name. If you don't see one, create a custom format using DDDD for the full day name (Monday, Tuesday) or DDD for the three-letter abbreviation (Mon, Tue).