Why is my Excel date format not changing?

Usually because the cell does not hold a date at all: it holds text that looks like one. Date formats act on stored serial numbers, and a text entry has none, so the format appears to do nothing. Text dates are typically left-aligned in the cell, while real dates sit right-aligned by default. Convert the text with the DATEVALUE function and then apply the format; changing the format alone never converts text into a date.

How do I convert text to a date in Excel?

Use DATEVALUE. In a helper column, enter =DATEVALUE(A2) pointing at the first text date and fill it down; it returns each date's serial number. Copy the helper cells, paste them over the originals with Paste Special set to Values, apply a date format so the serial numbers display as dates, and delete the helper column. DATEVALUE reads text in a date format Excel recognizes; if the year is missing it uses the current year from the computer's clock, and any time portion of the text is ignored.

How do I convert a 5-digit number to a date in Excel?

That number almost certainly is the date. Excel stores dates as serial numbers counted from January 1, 1900, so recent dates are 5-digit numbers in the 40,000s, and a cell formatted as General shows the raw count. Select the cells, open Format Cells, and apply a format from the Date category; the serial number displays as the date it represents. Nothing about the value changes, only the display.

How do I stop Excel from changing my numbers into dates?

Microsoft documents that this automatic conversion cannot be switched off, only worked around. Format the cells as Text before typing (select them, press Ctrl+1, choose Text, and select OK in Excel for Windows; Home, then Number Format, then Text in Excel for the web), or start each entry with an apostrophe, which is not displayed after you press Enter. For fractions like 1/2, type 0 1/2: the zero does not remain, and the cell becomes the Fraction number type instead of 2-Jan.

How do I change the Excel date format to dd/mm/yyyy?

Open Format Cells, select the Number tab, choose Custom in the Category list, and enter dd/mm/yyyy in the Type box. That displays day first with a four-digit year regardless of regional settings, because custom codes stay fixed while the asterisk-marked formats in the Date category follow the system's regional date settings. To adopt a whole locale's conventions instead of one pattern, pick a language in the Locale (location) box of the Date category.

Excel date formats: how dates really work and how to change them

Q: How do I change the date format in Excel?

Select the cells, open the Format Cells dialog (press Ctrl+1 in Excel for Windows, or Command+1 or Control+1 in Excel for Mac), select the Number tab, choose Date in the Category list, and pick a format under Type; in Excel for Windows, select OK to apply. In Excel for the web, select Home, open the Number Format dropdown, and choose Short Date or Long Date. Changing the format changes only how the date is displayed, not the value the cell stores.

An Excel date is a serial number: a plain count of days, with January 1, 1900 as day 1. The date format on the cell decides how that number looks and nothing more; changing the format never changes the stored value. To change how a date displays, select the cells, press Ctrl+1 in Excel for Windows (Command+1 or Control+1 in Excel for Mac), select the Number tab, choose Date in the Category list, and pick a format under Type; in Excel for Windows, select OK to apply.

One fact explains nearly every date problem this page covers: the date is a number, and the format decides only how it is displayed. That covers dates showing up as 5-digit numbers, formats that refuse to change, text that looks like a date but will not sort, and the dd/mm versus mm/dd confusion.

An Excel date is a serial number

Microsoft’s documentation states it plainly: “Excel stores dates as sequential serial numbers so that they can be used in calculations.” The count opens with January 1, 1900 as serial 1; by January 1, 2008 it has climbed to 39448, 39,447 days along. July 5, 2011 is 40729, the worked example on Microsoft’s date-systems page, and counting forward from there puts dates in mid 2026 in the 46,000s: July 4, 2026 is 46207. Because a date is a number, formulas can add to it, subtract one date from another, and compare dates, which is the entire point of the scheme.

What you see in the cell is that serial number displayed through whatever number format the cell carries:

	A	B
1	Number format	What the cell shows
2	General	40729
3	m/d/yyyy	7/5/2011
4	dd/mm/yyyy	05/07/2011
5	dddd, mmmm d, yyyy	Tuesday, July 5, 2011
6	yyyy-mm-dd	2011-07-05

Every cell in B2:B6 stores the same value: 40729, the serial number Microsoft's date-systems page gives for July 5, 2011. Only the format differs. Rows 3 and 4 are the mm/dd versus dd/mm trap in miniature: the same stored day prints as 7/5/2011 or 05/07/2011.

One wrinkle is worth knowing before it bites: Excel has two counting schemes. In the 1900 date system the count starts at January 1, 1900; in the 1904 date system it starts at January 1, 1904, so the same date’s serial number is 1,462 days smaller (July 5, 2011 is 40729 in one and 39267 in the other). Per Microsoft, the 1900 system is the default in Excel for Windows, Excel 2016 for Mac, and Excel for Mac 2011, while the 1904 system was the default in earlier versions of Excel for Mac. It survives as a per-workbook setting called Use 1904 date system, mostly in files with a long Mac history, and it is the classic explanation for every date in an imported workbook being off by exactly four years and a day.

How do you change the date format in Excel?

In Excel for Windows: select the cells, press Ctrl+1, and in the Format Cells dialog select the Number tab, then Date in the Category list. Pick a format under Type; the Sample box previews it with the first date in your data. Select OK. In Excel for Mac, press Command+1 or Control+1 and the same dialog appears. In Excel for the web there is a shorter menu: select Home, open the Number Format dropdown, and choose Short Date or Long Date.

Two details in the Type list matter more than they look:

The asterisk entries. Formats that begin with an asterisk (*) track the operating system’s regional date settings, so the display can change from one computer to another. Formats without one stay fixed wherever the file opens.
Nothing you pick here edits the data. Applying a format rewrites the display, not the serial number underneath. Set the format back to General and the raw count reappears, intact.

Custom date format codes

If no built-in format fits, build one. Microsoft’s route: pick the built-in date format closest to what you want, then go back to the Category list, choose Custom, and edit the code shown in the Type box. The built-in format is not affected; your edit is saved as a new custom format. The codes:

Days: d (1 to 31), dd (01 to 31), ddd (Sun to Sat), dddd (Sunday to Saturday)
Months: m (1 to 12), mm (01 to 12), mmm (Jan to Dec), mmmm (January to December), mmmmm (the month’s first letter)
Years: yy (00 to 99), yyyy (1900 to 9999)

So dd/mm/yyyy gives 05/07/2011, and dddd, mmmm d, yyyy gives Tuesday, July 5, 2011. One documented trap when a format includes time: an m immediately after h or hh, or immediately before ss, means minutes, not months.

These same codes also work inside the TEXT function, which produces an actual text string in that shape rather than a formatted date, useful for labels and concatenation. That is a different job with its own tradeoffs, covered in the TEXT function guide.

dd/mm/yyyy versus mm/dd/yyyy: regional formats

Excel’s default date display is not Excel’s decision. When you type a date, it appears in a default format that comes from the operating system’s regional settings (the Control Panel, on Windows), and it changes if those settings change. Type 2/2 and Excel assumes a date and shows something like 2-Feb, in the form the regional settings lead it to.

That gives you three levers:

Force one fixed pattern with a custom code such as dd/mm/yyyy, which displays day first with a four-digit year on every machine, because custom codes do not follow regional settings the way the asterisk formats do. The unambiguous yyyy-mm-dd, the ISO order, is built the same way.
Adopt another locale’s conventions via the Locale (location) box that appears in the Date category of the Format Cells dialog, which displays dates in the format of the language you pick.
Change the system default in the operating system’s regional settings, which moves every asterisk-formatted date in every workbook.

Two-digit years deserve their own warning. Per Microsoft, a year typed as 00 to 29 is read as 2000 to 2029, and 30 to 99 as 1930 to 1999: typing 5/28/19 gets you May 28, 2019, and a 1929 invoice date typed as 5/28/29 quietly becomes 2029. The cutoff is adjustable in the Windows Control Panel, but the safer habit is to always type four-digit years.

Convert text to a date with DATEVALUE

Dates that arrive by import or paste are often not dates at all but text that reads like one. The tell, per Microsoft: dates formatted as text sit left-aligned in the cell, while real dates are right-aligned by default. Text dates cannot be filtered, sorted, or calculated as dates, which is exactly the job Microsoft documents DATEVALUE for: it converts a date stored as text into the serial number Excel recognizes.

=DATEVALUE("8/22/2011")

Returns 40777, the serial number of August 22, 2011 (the result printed on Microsoft's DATEVALUE page). The answer arrives as a plain number: apply a date format to the cell to see it as a date.

The documented cleanup for a whole column: put =DATEVALUE(A2) in a helper column pointed at the first text date and fill it down, copy the helper cells, paste them over the originals with Paste Special set to Values, apply a date format to those cells, and delete the helper. Three remarks from the function’s page are worth keeping in mind: the text must be a date in a format Excel recognizes, like 8/22/2011 or 22-MAY-2011 (which returns 40685); if the year is omitted, DATEVALUE fills in the current year from the computer’s clock; and anything outside January 1, 1900 to December 31, 9999 returns the #VALUE! error, whose relatives are catalogued in every Excel error explained.

Excel can also flag some of these cells for you. With background error checking on, including its rule for cells containing years represented as two digits (in Excel for Windows, under File > Options > Formulas), text dates with a two-digit year can get an error indicator whose menu offers to convert them to 19xx or 20xx dates directly. The wider family this belongs to, numerals of every kind stored as text, is covered in numbers stored as text in Excel.

A date is showing as a number instead

The reverse complaint, a cell showing 46207 or 40729 where a date should be, is the serial number showing through: the cell holds a perfectly good date under the General format. Apply any format from the Date category and the date comes back. Nothing was lost, because nothing changed but the display. And if the cell shows ##### instead, the column is usually just too narrow to display the whole value; widen it.

Why is my date format not working? Four failure modes

1. The date format will not change

You apply a date format and the cell keeps showing 8/22/2011 exactly as typed, or the format changes but every cell displays the same way. The usual cause: the cells hold text, not dates. A date format renders a serial number, and a text entry has no serial number to render, so as far as Excel is concerned there is nothing to do. Check the alignment (text sits left), then convert with DATEVALUE as above and apply the format afterward.

2. Excel does not recognize what you type as a date

Whether an entry is recognized as a date depends on the patterns the system’s regional settings lead Excel to expect, so a column of European-style 31.12.2025 entries typed under United States settings can land as text and stay that way. If DATEVALUE cannot read the text either, it is not in a date format Excel recognizes and needs restructuring before conversion.

3. Excel keeps turning your numbers into dates

The opposite problem: type 12/2, a part number like 11-53, or the fraction 1/2, and Excel helpfully makes it a date such as 2-Dec. Microsoft is blunt about this one: “Unfortunately there is no way to turn this off.” The documented workarounds, from its page on the behavior:

Preformat the cells as Text before entering the data: in Excel for Windows, select them, press Ctrl+1, choose Text, and select OK (in Excel for the web: Home > Number Format > Text).
Lead with an apostrophe, as in ’11-53 or ’1/47. The apostrophe is not displayed after you press Enter.
Lead with a space, though the space remains in the cell, and Microsoft recommends the apostrophe over the space when lookup functions like VLOOKUP or MATCH will run against the data.
For fractions, lead with a zero and a space: type 0 1/2 and the cell becomes the Fraction number type instead of 2-Jan; the zero does not remain.

A green triangle may then appear on some cells, Excel noting a number stored as text; if the entry is meant to be text, the documented response is to select the indicator and choose Ignore Error.

4. Dates sort into the wrong order

Real dates sort chronologically because Excel is sorting their serial numbers. A date stored as text does not sort as a date, which is precisely why DATEVALUE exists, so a column that mixes real and text dates comes out of a sort in an order nobody intended. No format or sort option fixes this: convert the text entries to real dates first, then sort.

Producing real dates in formulas: DATE and TODAY

Searches for a date formula usually want one of two functions, both of which return real serial-number dates. DATE builds one from three numbers:

=DATE(2026,7,4)

returns July 4, 2026 as a genuine date (serial 46207) that any date format can display and any formula can calculate with. Microsoft’s own advice on it echoes this page’s two-digit-year warning: use four digits for the year argument, because 07 could mean 1907 or 2007. TODAY() returns the serial number of the current date. If formulas themselves are the unfamiliar part, start with what a formula is in Excel.

The short version

An Excel date is a serial number counting days from January 1, 1900; a date in mid 2026 is a number in the 46,000s. The format decides only how that number is displayed.
Change the display with Ctrl+1 (Windows) or Command+1 (Mac), then Number > Date, or build a custom code like dd/mm/yyyy from the documented d, m, and y codes.
Default date formats follow the operating system’s regional settings; custom codes stay fixed everywhere. Type four-digit years: a two-digit year is read as 1930 to 1999 or 2000 to 2029.
A date showing as a 5-digit number just needs a date format. Text refusing to take a date format needs DATEVALUE, because it was never a date.
Excel’s conversion of entries like 12/2 into dates cannot be turned off; preformat cells as Text or lead with an apostrophe.
Formats are invisible in most reviews, and a date column can change meaning without a single value changing. When a workbook comes back edited and the dates look off, compare the two versions and confirm what actually moved.