In Excel Power Query, set a column’s data type to Date to make its values usable as dates; set the displayed format separately after loading the results into Excel. If text dates are being read incorrectly, use Change Type > Using Locale and choose the locale that matches the source data.
Date type and date format are different
A date’s type determines what Power Query understands the value to be. A value with the Date type can be sorted chronologically, filtered by date, used in date calculations, and loaded into Excel as a date. A format determines how that value looks on screen, such as 7/3/2025, 03/07/2025, or 3 July 2025. Those displays may represent the same date.
Power Query is principally a data preparation tool, not a worksheet cell-formatting tool. Its preview shows how values are represented in the editor, but choosing a display pattern there does not set the number format of the Excel cells that receive the query results. After loading, format the date column in the worksheet if you want a particular appearance. Keeping dates as dates, rather than turning them into text just to change their appearance, preserves their usefulness in sorting, calculations, PivotTables, and other Excel features.
Set a column to the Date data type
In Microsoft 365 for Windows, open the query in Power Query Editor, select the date column, and use Transform > Data Type > Date. You can also use the data-type icon at the left of the column header. In some editor layouts, the equivalent command is available from the column’s right-click menu under Change Type. Excel adds a Changed Type step to the query’s Applied Steps list; that step is part of the repeatable transformation applied when the query refreshes.
Choose the type that reflects the source, rather than choosing Date automatically for every column containing a date-like value:
- Date represents a calendar day, with no time of day.
- Date/Time represents a date and a clock time.
- Date/Time/Timezone includes a time-zone offset, which matters when the source records one.
- Text is appropriate when the characters are labels or codes rather than dates you need to calculate with.
If a value includes a time, converting it to Date removes the time component. If that time matters, retain Date/Time instead. Conversely, a date-only column usually does not need a time type. Check the preview after changing the type: a calendar icon in the header indicates a date-related type, while an ABC icon indicates text.
Parse text dates with the correct locale
Locale is crucial when the source stores dates as text. For example, 04/05/2025 can mean April 5 in month/day/year order or 4 May in day/month/year order. Power Query cannot reliably infer which interpretation the source intended from that string alone. A computer’s regional settings, the workbook author’s location, and the source file’s conventions may differ.
To specify the interpretation, select the column and choose Transform > Data Type > Using Locale. In the dialog, select Date as the data type and choose the locale that matches the text’s order and conventions, such as English (United Kingdom) for day/month/year or English (United States) for month/day/year. Depending on the editor layout, you may find the command by right-clicking the column and choosing Change Type > Using Locale. Confirm that the preview shows the intended dates before loading the query.
Using Locale is a parsing instruction; it does not merely change how an already-valid date is displayed. For example, converting 04/05/2025 with a day/month/year locale produces May 4, while using a month/day/year locale produces April 5. Once the text is converted to a Date value, Excel can display it in another format without changing which day it represents.
Control the display after loading to Excel
Close and load the query, then select the date cells in the resulting worksheet table. On Windows, use Home > Number and choose a date format from the Number Format list. For more choices, open Format Cells with Ctrl+1, select the Number tab, and choose Date or Custom. On Mac, Command+1 opens Format Cells; ribbon labels and layout can vary slightly by Excel version.
For a custom appearance, enter a pattern such as dd mmm yyyy to show 07 Mar 2025, or mmmm d, yyyy to show March 7, 2025. In Excel number formats, mm means month when used with a date; use mmm or mmmm for abbreviated or full month names. Date format choices can be affected by regional settings, so a built-in Short Date format may look different on another computer. A custom format can make the intended presentation more explicit, though month names and separators may still reflect locale settings.
Formatting changes how a value appears, not its underlying date. If the worksheet displays a number such as 45723 instead of a familiar date, the cell may still contain a valid date serial with a General or numeric format. Apply a date number format to check. Do not replace the value with text unless you specifically need text output.
Format dates as text in Power Query only when needed
There are situations where the output really must be text—for example, building a fixed-format label for a report or combining a date with other text. In Power Query, the M function Date.ToText can return a formatted text value. In a custom column, an expression such as Date.ToText([OrderDate], [Format = "dd/MM/yyyy", Culture = "en-GB"]) formats the Date value in OrderDate as day/month/year text using the specified culture. Replace OrderDate with the actual column name.
This changes the result to text, so the new column will not behave like a date for date arithmetic or ordinary chronological sorting. Text sorting is usually alphabetical, which can put dates in an unexpected order. If the goal is only to make worksheet dates look different, keep the column typed as Date and apply a number format after loading instead.
For text that needs to be parsed, Date.FromText can accept an explicit culture. For example, in a custom column or other M expression, Date.FromText([DateText], [Culture = "en-GB"]) interprets DateText using the specified culture. If a source has an unusual but consistent pattern, the options record can also include a format, such as Date.FromText([DateText], [Format = "yyyyMMdd", Culture = "en-US"]). The source string must match the stated pattern; a culture choice cannot repair missing or invalid characters.
Fix common date conversion problems
If Power Query shows Error after a type change, inspect the original values rather than applying a different display format. Common causes include mixed date conventions, extra spaces, blank strings, words such as “unknown,” invalid calendar dates, or timestamps in a format the selected locale does not recognize. A column can contain mostly valid dates and still fail on a few exceptional rows.
- Check the source text: compare several representative rows, especially dates where both the day and month are 12 or lower. Those are the values most likely to hide an ambiguous interpretation.
- Check the conversion step: select the Changed Type step in Applied Steps and confirm its locale and chosen type. If the step guessed incorrectly, replace it with Using Locale.
- Inspect errors: use the error indicators or filter the column for errors to locate problematic rows. Decide whether to correct the source, replace known exceptions, or keep an error for review.
- Handle blanks intentionally: an empty cell, a null value, and an empty text string are not always equivalent. Clean or replace source values before conversion if needed.
- Check for time information: if the source includes hours and minutes, try Date/Time rather than Date so the time is not discarded.
Another frequent issue is an automatically inserted Changed Type step near the beginning of a query. Automatic detection can be useful, but it may infer a type using a sample or an unexpected convention. Review that step when a newly imported file produces different results from the previous one. If the source format changes between refreshes, build an explicit cleaning and conversion process rather than relying on detection alone.
How Power Query and Excel handle dates together
A useful way to think about the process is that Power Query interprets and transforms the incoming value, while Excel controls the worksheet’s display after loading. Power Query steps are recorded and run again when the query refreshes; worksheet number formatting is a presentation choice applied to cells. The two settings solve different problems, and changing one does not necessarily change the other.
For example, a CSV might contain 2025-03-07. Power Query can convert that text to a Date value. Once loaded, Excel might display it as 3/7/2025 under one regional setup and 07/03/2025 under another. Those displays can refer to the same date. If instead the query returns the text “07/03/2025,” Excel treats it as text, and the result may not sort or calculate as a date. For a robust data table, preserve a Date column and format it in Excel; create a separate text label only when a downstream requirement calls for one.
For Excel for the web, the same basic distinction applies: Power Query transformations define the values and types, while worksheet formatting changes their display. Some Power Query authoring and editing capabilities vary by platform and account, so a Windows desktop workflow may be needed for a particular query operation. On Mac, worksheet cell formatting is available, but Power Query availability and editing features can differ by Excel release.
Frequently Asked Questions
How do I change the date format in Power Query?
Set the column to the Date type in Power Query, then load the results and apply a number format in Excel. Use Date.ToText inside Power Query only when the output must actually be text.
Why does Power Query change my dates to US format?
The text may be parsed using a US month/day/year convention, or an automatic type-detection step may have chosen that interpretation. Use Change Type > Using Locale and select the locale matching the source data. If the value is already a true date, its worksheet display format—not the parsed date—may be what looks US-style.
Can I use dd/mm/yyyy in Power Query?
Yes. To interpret text in that order, use Using Locale with a suitable day/month/year locale. To display a genuine date that way in Excel, format the loaded cells with a custom format such as dd/mm/yyyy. These are separate tasks: parsing text and displaying a date.
Why are my dates showing as numbers in Excel?
Excel stores dates as numeric values and displays them using a number format. Select the cells and apply a date format from Home > Number, or open Format Cells with Ctrl+1 on Windows or Command+1 on Mac. If the values remain numbers, confirm that the query column is typed as Date rather than text or an unrelated numeric field.
Should I convert dates to text to keep their format?
Usually not. A Date value is more useful for sorting, filtering, and calculations, while text is best reserved for fixed labels or export requirements. Apply a worksheet number format when you only need a different visual style.
Related
- How to Record an Entire PowerPoint Presentation
- Online Budget Spreadsheet Free? Options, Costs and Setup
- Office Inventory Spreadsheet: Setup, Tracking, and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Find Duplicates in Excel and Google Sheets by FormulaIn Excel, select the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values;…
- How to Make Columns in Word on One Page Only: A GuideTo put columns on just one page in Microsoft Word, isolate that page in its own section, then…
- How to Add an Image Link to an Excel Cell in Microsoft 365To put a clickable link to an image in an Excel cell, select the cell and press Ctrl+K…
- Freeze Panes in Excel: Keep Rows and Columns VisibleTo freeze panes in Excel on Windows 11, select the cell immediately below the rows and to the…
- CSV File to Excel Converter: Convert Without Data LossTo convert a CSV file to an Excel workbook in Microsoft Excel for Microsoft 365 on Windows, select…
- How to Color an Excel Spreadsheet: Cells, Rows, and MoreTo color cells in Excel for Microsoft 365 on Windows, select the cells, open the Home tab, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.