DateTime.FromText

Updated on

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
ArgumentAttributeDescription
TextThe text to convert into a datetime value to transform into text.
OptionsOptionalThis 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 creates a datetime value from a textual representation, text. You can use an optional record parameter to provide additional instructions for the conversion.

Here’s what you can include in that record:

  • Format: Specifies the format you want for the date. If you leave this field empty or use ‘null,’ the function will do its best to understand the date format for you. Use any of the custom formats provided later in this post.
  • Culture: This comes into play when you’ve defined a ‘Format’. It adjusts certain format elements based on cultural settings. For instance, in the U.S., “MMM” would mean “Jan,” “Feb,” “Mar,” etc., whereas in Russia, it would translate to “янв,” “фев,” “мар,” and so on. If ‘Format’ is left as ‘null,’ the ‘Culture’ sets the default date format. When you don’t specify ‘Culture,’ the function defaults to the current system setting (using Culture.Current).

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 turn 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

Power Query might not recognize some date and time formats automatically. For such cases, you can specify a custom format string.

Take, for instance, the text ‘2024-04-15T03:45:12Z’. You can convert this to a DateTime value as follows:

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

A string with a different format is ‘20241231T055900’. You can extract a datetime value by 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 formatting can differ between countries. For example, the Dutch use ‘mrt’ to denote the month of March. If your text reflects such regional variations, you can specify both a format string and a culture code to ensure accurate conversion.

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

For your convenience, a list of available custom format strings can be found in the table below. These can also be combined with custom format strings for date formats and time formats.

FormatDescription12/31/2023 1:30:2512/29/2023 22:04:50.36973183
gGeneral date short time31/12/2023 01:3029/12/2023 22:04
GGeneral date long time31/12/2023 01:30:2529/12/2023 22:04:50
sShort time format2023-12-31T01:30:252023-12-29T22:04:50
uUniversal full format2023-12-31 01:30:25Z2023-12-29 22:04:50Z
fFull date short time31 December 2023 01:3029 December 2023 22:04
FFull date long time31 December 2023 01:30:2529 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