To show a real Excel date as yyyy/mm/dd, select the cells, press Ctrl+1, choose Number > Custom, enter yyyy/mm/dd in Type, and select OK. If the cell contains text rather than a date value, number formatting alone will not convert it; convert the text first, then apply the format.
What yyyy/mm/dd means in Excel
The pattern has a four-digit year, a two-digit month, and a two-digit day, with slashes between them. For example, March 8, 2025 appears as 2025/03/08. The leading zeroes matter: they keep the month and day at a consistent width and make the format easier to scan.
In Excel, dates entered as dates are stored as serial numbers and displayed using a number format. The value behind a cell displayed as 2025/03/08 is still a date that Excel can sort, subtract, or use in formulas. The format changes how the value looks; it does not turn the date into a string of characters.
Excel’s date-format letters are case-sensitive in meaning. Use lowercase m for month and lowercase d for day; yyyy displays a four-digit year. Uppercase M is not the standard month code to enter in a custom date format. A pattern such as yyyy/mm/dd is different from yyyy/dd/mm, which swaps the positions of the month and day.
Format real dates as yyyy/mm/dd
In Excel for Microsoft 365 on Windows, select the cells and press Ctrl+1 to open Format Cells. On the Number tab, select Custom, click the Type box, enter yyyy/mm/dd, and click OK. You can select an entire column before applying the format if it contains dates throughout. On a Mac, Command+1 opens the Format Cells dialog; the same Custom format code applies.
Another route is Home > Number Format, then More Number Formats to open the formatting options. If you choose a built-in date style instead, Excel may show a different order or separator based on your regional settings. A custom format gives you more control over the displayed pattern.
Some regional settings display the locale’s date separator even when the format code contains a slash. If you need a literal slash in the display, try the custom code yyyy"/"mm"/"dd. Check a sample cell after applying it, since separators and available formatting options can vary by locale and Excel version.
To apply the format to new entries as well, format the destination cells or column before entering dates. Excel normally carries a column’s formatting down as you type, but pasted data can bring its own formatting or values. Check the result after pasting rather than assuming the destination format has taken effect.
Tell a date value from date-looking text
A cell can display 2025/03/08 while containing either a true Excel date or text. The distinction matters: a real date can participate in date arithmetic and sort chronologically, while text may sort alphabetically and may not work in date calculations. Formatting a text cell changes neither its underlying type nor its contents.
One clue is alignment: numbers and dates are often right-aligned by default, while text is often left-aligned. But alignment can be changed, so it is not proof. You can also test a cell with a simple calculation: if A2 contains a date value, entering =A2+1 in another cell should return the following day, displayed according to that second cell’s format. If A2 contains text, Excel may return an error instead.
To return a formatted text result from an actual date, with the date value in A2, use =TEXT(A2,"yyyy/mm/dd"). For a date of March 8, 2025, the formula returns the text 2025/03/08. This is useful when a system specifically requires text, but the result is text, not a date. Keep the original date value if you will later sort, calculate, or filter using date logic.
Convert text in yyyy/mm/dd form into dates
If A2 contains text exactly like 2025/03/08, with the year first and two-digit month and day, this formula builds a real date from its parts:
With the text in A2, enter: =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)). For 2025/03/08, it returns the date March 8, 2025. Format the formula cell as yyyy/mm/dd to show the same year-first appearance. The formula assumes the text follows that exact ten-character pattern; a different separator or missing leading zeroes may require a different approach.
For a large range, enter the formula in the first result row and fill it down. Then check several results, including the first and last rows and any dates near month-end. If you need to replace the original text, copy the converted results and use Paste Special > Values over the source cells. Keep a backup or leave the original column intact until you have verified the conversion.
Excel’s DATE function can normalize invalid parts instead of rejecting them. For example, a month or day outside its usual range may roll into another month. Therefore, a result that looks like a valid date does not prove that every source string was valid. Review the input format and investigate unexpected results before relying on a bulk conversion.
Convert a column during import
Text dates commonly arrive from CSV files, reports, and databases. If you open a CSV directly, Excel may interpret fields using the computer’s regional date settings. A year-first value may be recognized as a date in one setup and left as text in another. After importing, inspect the data type and a few sample dates before building formulas or reports.
For a controlled conversion, use Data > From Text/CSV to preview and import the file. In the Power Query editor, check the date column’s type and, when necessary, choose Transform > Data Type > Using Locale to specify how the text should be interpreted. The exact choices depend on the column and available locales. Load the data, then apply the display format in the worksheet if you want the visible yyyy/mm/dd pattern.
You can also use Text to Columns on an existing worksheet range. Select the text column, choose Data > Text to Columns, select Delimited and continue through the wizard, then choose Date: YMD in the final step before selecting Finish. This option is intended for data whose components really are in year-month-day order. Save a copy first if the source column is important, and verify the converted values afterward.
Choose the right approach for your task
The best method depends on whether the priority is appearance, calculation, or a text export. For normal spreadsheet work, keep dates as real date values and change only their number format. That preserves date sorting, filtering, and arithmetic. Convert a text string to a date when Excel has not recognized it. Use TEXT only when another process explicitly needs a text string.
- Display dates consistently: apply the custom format
yyyy/mm/ddto real date cells. - Make text dates usable in calculations: convert them with a suitable formula or import tool, then verify the result.
- Produce a text field for export: use TEXT with the desired pattern, but remember that the result cannot be treated as a date without conversion.
- Preserve the original source: put converted results in a new column until you have checked them.
Year-first text with padded month and day can sort in chronological order as text when every value uses the same pattern. For example, 2025/03/08 comes before 2025/11/02 in ordinary ascending text order. That convenience does not make text dates equivalent to dates: blanks, inconsistent padding, extra spaces, or mixed formats can disrupt sorting and calculations. Real date values remain the safer choice for analysis.
Common problems and how to fix them
- The display does not change: the cells may contain text. Convert the values first, or use a formula that extracts the year, month, and day.
- The date shows as a number: the cell may be using a General or numeric format. Apply the custom date format; Excel’s underlying serial number is expected for a real date.
- The date is wrong by a month or day: the source may have been interpreted using a different regional order. Re-import or convert using the correct year-month-day interpretation, and compare with the original source.
- Some rows convert but others do not: the column may mix real dates, text, blanks, or different string patterns. Check the outliers individually instead of assuming one conversion rule fits every row.
- The slash looks different: regional date separators can affect display. Try quoting the slash in the custom format and inspect the result on the computer where the workbook will be used.
Excel also supports both the 1900 and 1904 date systems, which use different serial-number origins. Workbooks using different systems can show date differences when values are transferred between them. This is unusual in routine editing, but it is worth checking if dates shift substantially after copying data between older or separately configured workbooks.
Frequently Asked Questions
Does yyyy/mm/dd change the date stored in Excel?
No. When applied as a number format to a real date, it changes only the display. The underlying date remains available for calculations and sorting.
Why does formatting a cell not convert its date text?
Number formats control how numeric values and dates appear. Text is a sequence of characters, so it must be parsed or converted into a date value before a date number format can display it differently.
Should I use yyyy/mm/dd or TEXT(A2,”yyyy/mm/dd”)?
Use the custom number format when A2 should remain a date. Use TEXT when you deliberately need a text result, such as for a system that expects a formatted string.
Will yyyy/mm/dd always sort correctly?
Consistent, zero-padded text in year-month-day order sorts chronologically in ordinary ascending text order. Real Excel dates are more reliable when you need date calculations, filters, or mixed data cleanup.
Can I type 2025/03/08 directly into Excel?
Excel may recognize it as a date, depending on regional settings and input context, but it may also store it as text. Check the cell’s behavior and apply the desired format; use an apostrophe before an entry only when you intentionally want to preserve it as text.
Related
- Power Query Editor: How to Split Text into Multiple Columns
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Detect Circular References in Excel: Step-by-StepTo detect a circular reference in Excel for Microsoft 365 on Windows, open the Formulas tab, choose Error…
- Excel Shortcut to Paste Formatting and Keep DataIn Excel for Microsoft 365 on Windows, copy the formatted cell, select the destination, then press Ctrl+Alt+V, press…
- Excel Files List: Common Formats, Extensions, and UsesThe main Excel file types are .xlsx, .xlsm, .xlsb, and the older .xls format; Excel also uses templates…
- Google Sheets Date Format: Set DD MM YYYY CorrectlyTo display dates as day, month, and four-digit year in Google Sheets, select the cells and choose Format…
- Excel Scatter Plot Colors by Group: A Step-by-Step GuideTo color an Excel scatter plot by group, put each group in its own data series, then assign…
- Excel Power Query: Combine Files From a Folder EasilyIn Excel for Microsoft 365 on Windows, choose Data > Get Data > From File > From Folder,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.