Date.FromText

Updated on

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
ArgumentAttributeDescription
TextThe text value to convert into a date.
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 = “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, 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

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.

FormatDescription31/12/202301/02/2003
%dSingle Digit Day – 0 to 30311
ddDouble Digit Day – 00 to 303101
dddShort Weekday NameSunSat
ddddFull Weekday NameSundaySaturday
%MSingle Digit Month – 1 to 12122
MMDouble Digit Month – 01 to 121202
MMMShort Month NameDecFeb
MMMMFull Month NameDecemberFebruary
%yYear – 0 to 99233
yyYear – 00 to 992303
yyyYear with at least three digits20232003
yyyyFour-Digit Year20232003
yyyyyFive-Digit Year0202302003
m, MDay followed by Full Month Name31 December1 February
y, YStandard Long DateDecember 2023February 2003
dStandard Short Date31/12/202301/02/2003
DFull Long Date31 December 202301 February 2003
%g, ggThe period of an eraA.D.A.D.

Learn more about Date.FromText in the following articles:

Other functions related to Date.FromText are:

BI Gorilla Blog

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