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 the format of the date itself — you work within Excel's Format Cells dialog. If you need to change how Excel interprets dates when you type them, or adjust the calendar system Excel uses, you go to your computer's regional settings, which Excel reads automatically.

Most date problems in Excel come from the second source: your operating system's locale settings. Excel doesn't store its own date format preferences separately. It pulls them from Windows (or Mac) settings. So if your dates look wrong, the fix often starts outside Excel.

Key Takeaways

  • To change how a date looks on screen, select the cells, right-click, choose Format Cells, and pick a date format from the Number tab.
  • Excel reads your computer's regional settings to decide what date format to use by default, so changing that setting affects all your spreadsheets.
  • If dates appear as numbers like 45000, the column is too narrow or the cells are formatted as numbers instead of dates.
  • The date system Excel uses (starting from 1900 or 1904) is set per workbook and rarely needs to change unless you're moving files between Windows and Mac.

Changing How Dates Display in Your Spreadsheet

Select the cells containing the dates you want to reformat. You can click one cell, or drag to select a range, or click the column header to select the entire column. Then right-click and choose Format Cells. On Windows, you can also press Ctrl+1. On Mac, press Cmd+1.

In the Format Cells dialog, click the Number tab. On the left side, click Date from the Category list. The middle panel shows available date formats — you'll see options like "3/14/2024", "14-Mar-2024", "March 14, 2024", and others. Click the format you want. The preview at the bottom shows how your dates will look. Click OK when you're done.

If you don't see a format you like, you can create a custom one. In the same Number tab, select Custom from the Category list. In the Type field, you'll see format codes like "mm/dd/yyyy" or "dddd, mmmm d, yyyy". You can edit these codes directly. For example, "yyyy-mm-dd" displays dates as 2024-03-14. After you type your custom format, click OK.

Fixing Dates That Show as Numbers

If your dates appear as long numbers like 45000 or 45347, one of two things happened: the column is too narrow to display the date, or the cells are formatted as numbers instead of dates.

First, try widening the column. Position your cursor on the border between two column headers until it becomes a resize arrow, then drag to the right. If the dates now display correctly, you're done.

If widening the column doesn't help, the cells are formatted as numbers. Select the cells, right-click, choose Format Cells, click the Number tab, select Date from the Category list, pick a format, and click OK. The numbers will convert to readable dates.

Changing Your Computer's Default Date Format

Your operating system has a regional setting that tells Excel what date format to use when you first type a date or create a new spreadsheet. On Windows, open Settings, go to Time & Language, then Region. Scroll down to find the date format options. You'll see "Short date format" and "Long date format" — these control how dates appear by default in Excel and other programs.

On Mac, open System Preferences, click Language & Region, and look for the date format dropdown. Choose the format you want. Close the settings window. The next time you open Excel or create a new spreadsheet, it will use your new default format.

Changing your regional settings affects all programs on your computer, not just Excel. If you only want to change the format in one spreadsheet, use the Format Cells method instead.

Understanding Excel's Date System and Calendar

Excel uses one of two date systems depending on your computer. Windows Excel uses the 1900 date system, which counts January 1, 1900 as day 1. Mac Excel uses the 1904 date system, which counts January 1, 1904 as day 1. This matters when you move files between Windows and Mac — dates can shift by four years if the system isn't set correctly.

To check or change which system your workbook uses, open the file in Excel. On Windows, click File, then Options, then Advanced. Scroll down to the "When calculating this workbook" section and look for "Use 1904 date system". Check or uncheck this box depending on which system you need. On Mac, click Excel in the menu bar, then Preferences, then Calculation, and look for the same option.

Most users never need to change this setting. It only matters if you're working with very old files or sharing files regularly between Windows and Mac systems.

Handling Dates That Won't Format Correctly

Sometimes you select cells, apply a date format, and the dates still don't look right. This usually means the cells contain text that looks like a date, not an actual date value. Excel stores real dates as numbers. Text that looks like a date is just text.

To check, click one cell and look at the formula bar at the top. If the formula bar shows a number like 45000, it's a real date. If it shows text like "3/14/2024" with an apostrophe before it, it's text. To convert text dates to real dates, you'll need to use a formula or the Text to Columns feature. Select the cells, go to the Data tab, click Text to Columns, click Next twice, make sure the column format is set to Date, and click Finish. This converts text dates to real dates that Excel can format.

Frequently Asked Questions

Why do my dates show as numbers when I open a file from someone else?

The file was probably created on a different system or in a different program. Right-click the cells, choose Format Cells, select Date from the Category list, pick a format, and click OK. If that doesn't work, the cells contain text instead of dates, and you'll need to use Text to Columns to convert them.

Can I change the date format for just one cell instead of the whole column?

Yes. Click the single cell, right-click, choose Format Cells, select Date, pick your format, and click OK. You can also select multiple individual cells by holding Ctrl (or Cmd on Mac) and clicking each one, then format them all at once.

What does the apostrophe before a date mean?

An apostrophe tells Excel to treat what follows as text, not a number or date. If you see '3/14/2024 in the formula bar, Excel is storing it as text. Delete the apostrophe in the formula bar and press Enter to convert it to a real date.

Will changing my computer's date settings affect files I already created?

No. Files you've already created keep their formatting. Changing your regional settings only affects new spreadsheets and new dates you type going forward. Existing files won't change.

How do I show the time along with the date?

Select the cells, right-click, choose Format Cells, click the Number tab, select Date from the Category list, and look for formats that include time, like "3/14/2024 2:30 PM". If you don't see one you like, go to Custom and create one using codes like "mm/dd/yyyy hh:mm:ss".