How to Change Date Format in Excel (And Why It Matters)

Date formatting in Excel is one of those things that looks simple on the surface — until you're staring at a column where half your dates show as numbers, your regional settings disagree with a colleague's, and a formula refuses to recognize what you're looking at as a date at all. Here's how it actually works.

What Excel Is Actually Storing When You Enter a Date

Before changing any format, it helps to understand what Excel does with dates under the hood.

Excel stores dates as serial numbers — plain integers counting from January 1, 1900. The date January 1, 2024, for example, is stored as the number 45292. The visual format you see in a cell is just a display layer on top of that number.

This is why formatting a date is non-destructive — you're only changing how the number is presented, not the value itself. It also explains why cells sometimes suddenly display a raw number instead of a date: the underlying value is still there, but the format has been stripped or reset.

The Most Common Method: Format Cells Dialog

The primary way to change a date format in Excel is through the Format Cells dialog.

1. Select the cell or range of cells containing your dates
2. Right-click and choose Format Cells (or press Ctrl + 1 on Windows / Cmd + 1 on Mac)
3. Click the Number tab
4. Select Date from the Category list
5. Choose a format from the Type list, or scroll to the bottom and select Custom to build your own

The built-in date formats include options like:

• 3/14/2012
• 14-Mar-12
• March 14, 2012
• Wednesday, March 14, 2012

These are pulled from your system locale settings, which is why the same Excel file can look different on two different computers.

Building a Custom Date Format 📅

If none of the presets match what you need, the Custom category lets you construct an exact format using format codes.

So if you want dates to appear as 2024-03-05, your custom format code would be: yyyy-mm-dd

To apply a custom format, select your cells, open Format Cells (Ctrl + 1), go to Number → Custom, and type your format code into the Type field.

Using the Quick Format Dropdown on the Ribbon

For basic switches, the Number Format dropdown in the Home tab ribbon gives you quick access to a short list of date options. It won't offer the full range of custom formats, but it's faster for common changes like switching between Short Date and Long Date.

When Dates Aren't Recognized as Dates ⚠️

This is where many users run into real trouble. If you import data from a CSV, another system, or a copied webpage, Excel may interpret dates as plain text rather than date values. In that case, formatting won't work — there's no serial number to format.

Signs your dates are stored as text:

• They align to the left side of the cell by default (numbers and dates align right)
• Formulas like =YEAR() or =DATEDIF() return errors
• Sorting doesn't produce chronological order

To convert text to real dates, you have several options depending on the format of the text:

• DATEVALUE() function — converts a date stored as text (e.g., "03/05/2024") into a serial number
• Text to Columns wizard — found under Data → Text to Columns, this can reparse text dates if you specify the date format during the process
• Power Query — for bulk imports and recurring data transformations, Power Query handles date type conversion more reliably
• Find & Replace combined with reformatting — works for simple cases where text just needs a trigger to re-evaluate

Regional and Locale Settings: The Hidden Variable

Excel's date behavior is closely tied to your operating system's regional settings. A date formatted as 04/05/2024 means April 5 in the US and May 4 in the UK. When files move between regions, this ambiguity can corrupt date logic silently — formulas may still calculate, but on the wrong dates.

If you're collaborating internationally, using ISO 8601 format (yyyy-mm-dd) eliminates this ambiguity entirely. It's also the safest format for any data that will be used in databases, APIs, or automated systems.

How the Variables Stack Up

The "right" approach to changing a date format depends on several factors that vary by user:

• Are your dates already recognized as dates, or stored as text?
• Which version of Excel are you on — desktop (Windows or Mac), Excel Online, or mobile?
• Where did the data come from — manual entry, import, a connected data source?
• Is the format just visual, or does it need to work across regional boundaries and formulas?
• How much of the data needs reformatting — a few cells or tens of thousands of rows?

Someone doing a quick fix on a single-user spreadsheet is in a very different position from someone building a shared template used across multiple regions. The formatting mechanics are the same, but which tool to reach for — Format Cells, Power Query, DATEVALUE, or a system-level locale change — shifts significantly depending on those conditions.

Your setup and what you're trying to accomplish with those dates is ultimately what determines which path is worth taking. 🗓️