DateTime.FromText Function (Power Query M)

DateTime.FromText parses a text value into a datetime value. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you have dates and times stored as text and you want to turn them into real datetime values you can sort, filter, and do date math on, this is the function you reach for.

It can read standard text on its own, and you can hand it a format and a culture when the text follows a layout it would otherwise misread.

Syntax of DateTime.FromText Function

DateTime.FromText(text as nullable text, optional options as any) as nullable datetime

where

  • text (required, nullable text). The text value to convert into a datetime.
  • options (optional, any). A record that controls how the text is read. It can contain two fields:
  • Format is a text value giving the layout to parse, written with .NET custom format specifiers like dd/MM/yyyy HH:mm. Leave it out or set it to null to let Power Query parse on a best-effort basis.
  • Culture is a text value like "en-GB" or "en-US". It decides things like day-first versus month-first order and the meaning of name-based specifiers. When omitted, the current culture is used.

You can also pass a bare text value instead of a record. That is the legacy form, and it is treated as the Culture with no Format.

Returns: a datetime value parsed from the text. If text is null, it returns null.

In plain terms, you give it a string and it gives you back a real datetime you can work with.

Example 1: Parse an ISO datetime string

Convert a standard ISO-style string into a datetime.

DateTime.FromText("2025-03-17T14:30:00")

Result: 2025-03-17T14:30:00

The text is in a format Power Query recognizes, so no options are needed.

Example 2: Parse a space-separated datetime

The same value with a space between the date and time instead of a T.

DateTime.FromText("2024-11-05 08:15:45")

Result: 2024-11-05T08:15:45

This layout is also recognized on its own, so the result still parses cleanly.

Example 3: Parse a UK day-first string with Format and Culture

Here the text is day-first (17/03/2025), which would parse wrong or fail if read as month-first.

DateTime.FromText("17/03/2025 14:30",[Format="dd/MM/yyyy HH:mm",Culture="en-GB"])

Result: 2025-03-17T14:30:00

The Format field spells out the day-first order and Culture="en-GB" confirms the UK reading, so 17 is the day and 03 is the month.

Example 4: Parse a custom-format timestamp

This timestamp has no separators, so Power Query cannot guess its layout.

DateTime.FromText("20250628T091500",[Format="yyyyMMdd'T'HHmmss",Culture="en-US"])

Result: 2025-06-28T09:15:00

The Format field matches the non-standard layout exactly, with the literal T quoted as 'T' so it is treated as text rather than a specifier.

Things to keep in mind with DateTime.FromText

  • Text that does not match the format throws an error. A value the parser can’t read, such as DateTime.FromText("not a date"), returns [DataFormat.Error] We couldn't parse the input provided as a DateTime value. Fix it by passing the right Format and Culture.
  • Ambiguous text can parse wrong silently. A string like 03/04/2025 is read as day-first or month-first depending on culture. Set Culture explicitly so you don’t get the wrong date with no error.
  • null input returns null. A blank or null cell passes straight through, so you don’t have to guard for it before calling the function.
  • For a date-only or time-only value, use the matching function. Reach for Date.FromText or Time.FromText instead of forcing the text through DateTime.FromText.
  • The Format string uses .NET custom format specifiers. Things like yyyy, MM, dd, HH, mm, and ss, with literal characters wrapped in single quotes.
  • It runs locally and doesn’t fold. Text parsing happens in the mashup engine, so on a database source the work won’t push down to the server.

Common questions about DateTime.FromText

What is the difference between DateTime.FromText and DateTime.From?

DateTime.FromText only accepts text and lets you control the layout with Format and Culture. DateTime.From is more lenient and accepts other types like numbers, which it reads as serial date values.

How do I handle a column with more than one date format?

Wrap the call in try ... otherwise so a row that fails one Format can fall back to another, or clean the column to a single layout before parsing. To convert a whole text column at once you can also use Table.TransformColumnTypes with a culture.

How do I go the other way and turn a date back into text?

Use Date.ToText for a date value, which formats it back to a string with the layout you choose.

List of All Power Query Functions

Related Power Query Functions / Articles:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.