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 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.
| 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 |
Related functions
Other functions related to DateTime.FromText are:
