Date.FromText - Date Function

Date.FromText is a Power Query M function that creates a date value from a textual representation, with an optional record parameter for additional properties. The function returns a date value based on the input text and formatting options.

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

Syntax

Date.FromText(
   text as nullable text,
   optional options as any,
) as nullable date

Argument	Attribute	Description
Text		The text value to convert into a date.
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 = “d MMM yyyy”, Culture = “nl-NL” ]. For supported format strings, see the table later in this post. If you omit this argument, the default format used will align with the culture of your system.

Description

The Date.FromText formula converts a text value into a date. 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, you can specify US formatting with “en-US”. With these rules “MMM” would mean “Jan,” “Feb,” “Mar,” etc., whereas in Russia (“ru-RU” ), 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

Let’s look at some examples of how to extract a date from a text value.

Converting Standard Date Format

Suppose you want to convert the text “2023-12-31” into a date value. You can use the code:

// Output: #date( 2010, 12, 31 )
Date.FromText( "2023-12-31" )

Using a Custom Format String

In the situation where Power Query does not recognize your string, you can provide it with a custom format string.

// Output: #date( 2023, 12, 31 )
Date.FromText( "2023-31-12", [Format="yyyy-dd-M" ] )

Language-Specific Formatting

Some notations differ depending on the language your report in. For example, in the Netherlands, the text “31 mrt 2023” is identical to the English “31 Mar 2023“.

The Dutch culture writes months in lower letters and uses a different abbreviation from English. To extract a date from this value, you can specify both a custom format string and a culture code.

// Output: #date( 2024, 3, 31 )
Date.FromText( "31 mrt 2024", [Format="dd MMM yyyy", Culture="nl-NL"] )

Using Culture Codes for Calendars

The culture code serves purposes beyond language; it can also adjust the calendar system in use. As an example, the code below takes a date from the year 1400 in the Hijri calendar and converts it to a Gregorian date:

// Output: #date( 1979, 11, 20 )
Date.FromText( "1400", [Format="yyyy", Culture="ar-SA"] )

Custom Format Strings: A Quick Reference

For your convenience, a list of available custom format strings can be found in the table below.

Format	Description	31/12/2023	01/02/2003
%d	Single Digit Day – 0 to 30	31	1
dd	Double Digit Day – 00 to 30	31	01
ddd	Short Weekday Name	Sun	Sat
dddd	Full Weekday Name	Sunday	Saturday
%M	Single Digit Month – 1 to 12	12	2
MM	Double Digit Month – 01 to 12	12	02
MMM	Short Month Name	Dec	Feb
MMMM	Full Month Name	December	February
%y	Year – 0 to 99	23	3
yy	Year – 00 to 99	23	03
yyy	Year with at least three digits	2023	2003
yyyy	Four-Digit Year	2023	2003
yyyyy	Five-Digit Year	02023	02003
m, M	Day followed by Full Month Name	31 December	1 February
y, Y	Standard Long Date	December 2023	February 2003
d	Standard Short Date	31/12/2023	01/02/2003
D	Full Long Date	31 December 2023	01 February 2003
%g, gg	The period of an era	A.D.	A.D.

Learn more about Date.FromText in the following articles:

Extract Date From Text String in Power Query
This post shows how to extract a date from text values in Power Query. You can convert almost any string to a date with this trick! » Read more

Other functions related to Date.FromText are:

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