How to Change the Date Format in Excel: A Complete Guide

Dates in Excel can be deceptively tricky. What looks like a date on your screen is actually a number underneath — and that number gets displayed differently depending on the format applied to the cell. Understanding this distinction is the key to changing date formats confidently, without accidentally breaking your data.

Why Excel Stores Dates as Numbers

Excel represents every date as a serial number — January 1, 1900 is 1, January 1, 2025 is 45658, and so on. The format applied to a cell simply controls how that number is displayed. This means the same underlying value can appear as 01/15/2025, 15-Jan-2025, January 15, 2025, or even just 15 depending on your formatting choice.

This matters because changing a date format never alters the actual data — it only changes the visual presentation. That's good news when you're formatting, but it also means that if a date isn't stored as a serial number (say, it was imported as plain text), formatting alone won't fix it.

How to Change the Date Format in Excel 📅

Method 1: Format Cells Dialog (Most Control)

This is the most flexible approach and works across all modern versions of Excel.

1. Select the cell or range containing your dates.
2. Right-click and choose Format Cells, or press Ctrl + 1 (Windows) / Cmd + 1 (Mac).
3. In the dialog, click the Number tab.
4. Select Date from the Category list.
5. Choose a format from the Type list (e.g., 14-Mar-2012 or 3/14/12).
6. Click OK.

The Type list shows a preview of how your date will appear before you confirm.

Method 2: Custom Date Formats

If none of the built-in formats match what you need, you can build your own using format codes:

To apply a custom format, follow the same steps as above but select Custom from the Category list and type your format code into the Type field. For example, dddd, mmmm dd, yyyy would display as Wednesday, March 05, 2025.

Method 3: The Home Ribbon (Quick but Limited)

On the Home tab, the Number group has a dropdown that includes a few preset date options — Short Date and Long Date. These are quick to apply but offer far less control than the Format Cells dialog. The exact appearance of Short Date and Long Date depends on your system's regional settings.

Method 4: TEXT Function (For Display in Formulas)

If you need a date formatted as text within a formula — for example, to combine it with other text — use the TEXT function:

=TEXT(A1, "dd/mm/yyyy") 

This converts the date value into a text string formatted as specified. Important caveat: the result is no longer a number, so it can't be used in date calculations. Use this for display purposes only.

Regional Settings and Their Effect on Date Formats 🌍

This is where many users run into unexpected behavior. Excel's default date display is influenced by your operating system's regional locale. A spreadsheet built in the US where mm/dd/yyyy is standard may display differently when opened on a machine set to a UK locale, where dd/mm/yyyy is the norm.

This doesn't change the underlying serial number — the actual date remains accurate — but the appearance will shift based on the regional setting of whoever opens the file. If you're sharing spreadsheets internationally, applying explicit custom formats (rather than relying on system defaults) helps ensure consistency.

When Dates Aren't Really Dates: The Text Problem

A common issue occurs when dates are imported from external sources — CSV files, databases, web exports — and land in Excel as text strings rather than proper date values. You'll recognize this when:

• Dates are left-aligned in the cell (numbers and real dates are right-aligned by default)
• Changing the format has no visible effect
• Formulas like DATEDIF or NETWORKDAYS return errors

In these cases, you need to convert the text to a date value first. Options include:

• DATEVALUE function: =DATEVALUE("15/01/2025") converts a text string to a serial number.
• Text to Columns wizard: Found under the Data tab, this can parse text dates into proper date values during the conversion process.
• Power Query: For large datasets with inconsistent date formats, Power Query gives you fine-grained control over data type conversion on import.

Which of these approaches is appropriate depends on the volume of data, how consistently the text dates are formatted, and how much automation you need in your workflow.

Variables That Affect Which Approach Works Best

Several factors determine which date formatting method makes most sense for a given situation:

• Data source: Dates entered manually behave differently from dates imported via CSV or pulled through Power Query.
• Excel version: Older versions of Excel have fewer built-in date format options; Power Query, for instance, wasn't available before Excel 2016.
• Platform: Excel on Mac and Excel on Windows share most formatting behavior, but regional defaults and some keyboard shortcuts differ.
• Use case: Formatting for visual presentation in a report is a different need than ensuring date values calculate correctly in a formula-heavy workbook.
• Sharing and collaboration: If the file will be used by people in different countries or on different regional settings, hardcoded custom formats behave more predictably than system-default formats.

The right combination of these factors — your version of Excel, where your data comes from, and what you ultimately need dates to do in your spreadsheet — shapes which of these methods will actually solve your problem cleanly.