Power Query Date to Text: Format Dates Correctly

To convert a date to text in Power Query, add a Custom Column with Date.ToText([OrderDate], "yyyy-MM-dd", "en-US"), replacing OrderDate with your column name. For a quick conversion using the default display format, change the column’s data type to Text; use an explicit format and culture when the exact result matters.

What date-to-text conversion changes

Power Query stores a date as a date value, not as a string that merely looks like a date. Converting it to text changes the value’s type: the result is a sequence of characters such as 2026-11-02, rather than a date that Power Query can directly use for date arithmetic and date-specific filtering.

This distinction matters because a date can be displayed in different ways without changing its underlying value. In Excel, formatting a cell as dd/mm/yyyy changes its appearance but leaves it a date. In Power Query, changing the type to Text or using Date.ToText creates text. A format applied in the Power Query preview is not just cosmetic when the column’s data type has been changed.

Convert dates to text when a destination specifically requires text, when building a label or key, or when producing a fixed-format export. If you still need to compare dates, calculate durations, group by month, or use a date filter, keep the original date column and make a separate text column for presentation or export.

Use a custom column for a specific format

In Excel for Microsoft 365 on Windows, open the query in Power Query Editor. Select Add Column > Custom Column, give the new column a name such as OrderDateText, and enter an M expression. For a column named OrderDate, a reliable year-month-day result is:

Date.ToText([OrderDate], "yyyy-MM-dd", "en-US")

The custom-column expression is M code, so it does not start with an equals sign. The function takes the date value, a format string, and a culture. Here, a date such as November 2, 2026 becomes 2026-11-02. The format gives a fixed-width year, month, and day, which is useful for consistent exports and text comparisons.

Other useful patterns include Date.ToText([OrderDate], "dd MMM yyyy", "en-GB"), which produces a result like 02 Nov 2026, and Date.ToText([OrderDate], "MMMM d, yyyy", "en-US"), which produces November 2, 2026. The culture controls language and regional conventions, including month names. Choose a culture deliberately if the query will be refreshed on computers with different regional settings.

After adding the column, check its data type in the column header or the ribbon. A custom column containing the result of Date.ToText should be text. When the query is ready, select Home > Close & Load to return its results to Excel.

Convert the existing column through the interface

If you do not need to control the exact string pattern, you can convert the existing date column. Select it, then choose Transform > Data Type > Text. You can also use the data type icon in the column header. This changes the selected column to text and Power Query renders the date using the applicable regional conventions.

For more control over regional interpretation, use Change Type > Using Locale from the column’s data type options or its right-click menu, where available. In the dialog, choose Text as the data type and select a locale. This is particularly relevant when a date is being interpreted from text that could mean different things in different regions. For example, 04/05/2026 could mean April 5 or May 4 depending on the convention.

There is an important difference between converting a genuine date value and interpreting a text value that resembles a date. If the source is already a date, converting it to text formats that value. If the source is text, first make sure Power Query parses it using the correct locale; otherwise, a date may be misread or return an error before you convert it back to text. Locale settings guide interpretation and output, but they do not repair an incorrect source value.

Choose the right format and culture

Format strings use tokens to specify the parts of a date. In common Power Query date patterns, yyyy represents a four-digit year, MM a two-digit month, and dd a two-digit day. Use uppercase M for month: lowercase m is associated with minutes in date-and-time patterns. For readable month names, use MMM or MMMM.

yyyy-MM-dd gives a fixed-width date such as 2026-11-02.
M/d/yyyy gives a compact numeric date such as 11/2/2026, but its meaning can be region-dependent.
dd MMM yyyy gives a readable result such as 02 Nov 2026.
dddd, MMMM d, yyyy includes the weekday, for example Monday, November 2, 2026.

For machine-readable exports, a fixed-width year-month-day pattern is often easier to sort as text than a pattern with variable-width month and day numbers. For example, text values in the form yyyy-MM-dd sort in calendar order when compared lexically, provided every value uses the same format and the year comes first. A format such as M/d/yyyy does not reliably sort in calendar order as text. If the destination accepts actual dates, retaining date values is usually safer than relying on text sorting.

Handle nulls, errors, and column names

Missing values deserve attention before conversion. Power Query represents a missing value as null; date-to-text functions generally preserve a null as null rather than turning it into a meaningful date string. If the destination requires a placeholder such as Unknown, add that rule explicitly rather than assuming a blank will become the desired label.

Errors often point to a type or parsing issue. A value that looks like a date may still be text, may contain an unexpected character, or may be invalid—for example, a day outside the range for its month. Inspect the step that first introduces the error and check the source type and locale. If some rows are valid and others are not, decide whether to correct the source, keep errors visible for review, or replace them according to a documented rule.

In a custom-column expression, refer to the actual column name inside square brackets. For example, if the column is called Order Date, use [Order Date] rather than [OrderDate]. Power Query uses the name in the query at that step, so a renamed or removed column can make a later expression fail. When working with a column that contains date and time values instead of date-only values, choose the function that matches its type rather than forcing it into a date function.

Dates, datetimes, and timestamps

A date value contains a calendar date; a datetime also contains a time of day. If a column is a datetime and you want to preserve the time in the text, use DateTime.ToText, for example DateTime.ToText([CreatedAt], "yyyy-MM-dd HH:mm:ss", "en-US"). In that pattern, uppercase HH represents a 24-hour clock and lowercase mm represents minutes. Check the source type before choosing a pattern so that you do not accidentally discard the time component.

If you want only the calendar date from a datetime, extract the date first, then format it. For example, with a datetime column named CreatedAt, use Date.ToText(DateTime.Date([CreatedAt]), "yyyy-MM-dd", "en-US"). This intentionally drops the time. It is not equivalent to formatting the full timestamp, and that difference can matter when records near midnight belong to different reporting dates.

Timezone-aware values need particular care. A timestamp with a timezone offset is not always the same thing as a local wall-clock time. If a report needs a particular timezone’s calendar date, decide how the value should be converted before turning it into text. Formatting alone does not convert a timestamp to a different timezone; it only represents the value according to the function and pattern used.

Keep the date and text versions when needed

A common design is to preserve the source date and add a text companion. The original date remains available for chronological sorting, date filters, grouping, and calculations; the new text column serves a system or report that needs a particular representation. This avoids having to parse the text back into a date later.

For example, an order table might keep OrderDate as a Date column and add OrderDateISO with Date.ToText([OrderDate], "yyyy-MM-dd", "en-US") for a CSV export. A reader can understand the text column, while other query steps can continue to use the proper date type. If the text is only for display in an Excel worksheet, it may be preferable to load the date as a date and format the worksheet cells instead.

When a query is refreshed, Power Query reapplies its recorded transformation steps to the incoming data. That makes an explicit format useful for repeatable output: the query applies the same pattern on refresh rather than relying on a person to reformat cells each time. Still, downstream systems may impose their own requirements for separators, leading zeros, or locale, so confirm the required format with the receiving system.

Common misconceptions and troubleshooting

Changing a worksheet’s number format does not convert a Power Query column to text. Conversely, changing a query column to Text is not merely a display choice: it changes the type of the loaded value. If an imported date appears differently after loading, distinguish among the Power Query value, the worksheet’s number format, and any formatting applied by a destination application.

Another frequent problem is relying on a locale-specific default. A date displayed as 11/2/2026 may be read differently by a recipient or another computer. An explicit pattern such as yyyy-MM-dd removes that ambiguity for the text representation, while an explicit culture makes month names and regional conventions predictable. It does not, however, validate that the original date was interpreted correctly.

If the output is not what you expect, check these points:

Confirm whether the source column is Date, Date/Time, Date/Time/Timezone, or Text.
Check whether the query parsed source text using the intended locale.
Use uppercase MM for month and make the format pattern explicit.
Verify the column name at the step where the custom expression is added.
Inspect nulls and errors separately instead of treating them as ordinary date values.

Frequently Asked Questions

How do I convert a date to text in Power Query?
Use Transform > Data Type > Text for a straightforward conversion, or add a custom column with Date.ToText([DateColumn], "yyyy-MM-dd", "en-US") when you need a specific pattern.

How do I format a date as dd/mm/yyyy?
Use Date.ToText([DateColumn], "dd/MM/yyyy", "en-GB"). The format sets the day-month-year order, and the culture makes regional conventions explicit. Check that the source is already a correctly parsed date.

Why does Power Query show a different date after conversion?
The conversion may be using a different regional default, or the source may have been parsed with the wrong locale. Check the source type and parsing step, then use an explicit format and culture for the text result.

Can I convert a datetime to text but keep the time?
Yes. Use DateTime.ToText with a pattern that includes the time, such as DateTime.ToText([CreatedAt], "yyyy-MM-dd HH:mm:ss", "en-US"). Use Date.ToText only when the value you want to format is a date.

Should I convert dates to text before loading them into Excel?
Only when the destination needs text. Keeping dates as dates preserves date filtering, sorting, and calculations. For worksheet appearance alone, load the date value and apply an Excel number format instead.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.