DateTime.FromText - DateTime Function

DateTime.FromText is a Power Query M function that creates a datetime value from a textual representation, with optional formatting and culture parameters.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
Text		The text to convert into a datetime value to transform into text.
Options	Optional	This is an optional argument where you can provide a record parameter with additional instructions. The `Format` field is used to specify the output format of the transformation. You can also provide a `Culture` field to ensure the transformation supports local formats. For example: [ Format = “dd MMM yyyy HH:mm:ss”, Culture = “nl-NL” ]. If you omit this argument, the default format used will align with the culture of your system, using Culture.Current.

Description

The DateTime.FromText function converts a text string into a DateTime value. It handles various date and time formats and allows you to specify a custom format string and culture code for regional variations. You can use an optional record parameter to provide additional instructions for the conversion.

To support older workflows, you can also pass in a simple text value for ‘options.’ This behaves as if you set ‘options’ to [Format = null, Culture = options].

Examples

Basic Conversion

Let’s say you have a table that includes the text "2023-07-31T05:30:19." You can easily convert this into a DateTime value using the following code:

// Output: #datetime( 2023, 7, 31, 5, 30, 19 )
DateTime.FromText( "2023-07-31T05:30:19" )

Specifying a Custom Format String

Sometimes, Power Query doesn’t recognize date and time formats automatically. In these cases, you can use a custom format string.

For example, let’s convert the text "2024-04-15T03:45:12Z" into a DateTime value:

// Output: #datetime( 2024, 4, 15, 3, 45, 12 )
DateTime.FromText( 
  "2024-04-15T03:45:12Z", 
  [ Format="yyyy-MM-dd'T'HH:mm:ss'Z'" ] 
)

For a string with a different format, like "20241231T055900", you can extract a DateTime value using the following formatting string:

// Output: #datetime( 2024, 12, 31, 5, 59, 0 )
DateTime.FromText( 
  "20241231T055900", 
  [ Format = "yyyyMMdd'T'HHmmss", Culture="en-US" ] 
)

Incorporating a Culture Code

Date and time formats vary across different countries. For instance, the Dutch use “mrt” for March. If your text includes regional variations, specify both a format string and a culture code to convert it accurately.

Here’s how to convert "2023 mrt 31 05:30:19.667899" to a DateTime value:

// Output: #datetime( 2023, 3, 31, 5, 30, 19.66789 )
DateTime.FromText( 
  "2023 mrt 31 05:30:19.667899", 
  [Format="yyyy MMM dd HH:mm:ss.ffffff", Culture="nl-NL"] 
)

Custom Format Strings for DateTime

Below are some custom format strings you can use. These can also be combined with custom format strings for date formats and time formats.

Format	Description	12/31/2023 1:30:25	12/29/2023 22:04:50.36973183
g	General date short time	31/12/2023 01:30	29/12/2023 22:04
G	General date long time	31/12/2023 01:30:25	29/12/2023 22:04:50
s	Short time format	2023-12-31T01:30:25	2023-12-29T22:04:50
u	Universal full format	2023-12-31 01:30:25Z	2023-12-29 22:04:50Z
f	Full date short time	31 December 2023 01:30	29 December 2023 22:04
F	Full date long time	31 December 2023 01:30:25	29 December 2023 22:04:50

Other functions related to DateTime.FromText are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/datetime-fromtext