Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Are you tired of manually trying to extract a date from text values in Power Query? It can be a real headache, especially when dealing with different date formats and culture codes. But this article is here to show you an effective approach using the Date.FromText function.

With this function, you’ll be able to automatically extract dates from text, extract a date based on a specific format, and even retrieve a date based on a format and culture code.

So what approach can you use to convert a data type from text to date?

Table of contents
Extract Dates From Text Strings in Power Query

Introducing Date.FromText

A very versatile function for turning text into a date is the Date.FromText function. As first argument, you can provide the function with a text value. If your value is an integer, please make sure to transform it to a text value first.

The Date.FromText function automatically tries to recognize the date format a text value has, making it a very powerful tool for data analysis. With this function, you can automatically extract dates from text, extract a date based on a specific format, and even retrieve a date based on a format and culture code.

So what does that work?

Auto Recognize Dates

One thing that’s really cool about the Date.FromText function is that it can automatically try to recognize a date format from a text value.

After providing the function with your text, Power Query tries to convert your text to a date value. The following text values transform successfully to a date value:

= Date.FromText( "20221231" )     // Returns #date( 2022, 12, 31 )
= Date.FromText( "31-12-2022" )   // Returns #date( 2022, 12, 31 )
= Date.FromText( "2022-12-31" )   // Returns #date( 2022, 12, 31 )
= Date.FromText( "2022/12/31" )   // Returns #date( 2022, 12, 31 )
= Date.FromText( "31.12.2022" )   // Returns #date( 2022, 12, 31 )
= Date.FromText( "01,01,2022" )   // Returns #date( 2022, 1 , 1  )
= Date.FromText( "1997/1/1" )     // Returns #date( 1997, 1 , 1  )
= Date.FromText( "1/1/1997" )     // Returns #date( 1997, 1 , 1  )
= Date.FromText( "12 MAR 22" )    // Returns #date( 2022, 3 , 12 )
= Date.FromText( "31..12..2022" ) // Returns #date( 2022, 12, 31 )
Extract date from text

When Power Query recognizes the text as a date, it automatically converts the string to a date.

Note

Note that results may differ depending on the culture of your device. A computer working on US system settings, recognizes dates different than a Dutch one. Later this article, we cover how you can work around this using a formatting string or a culture code.

Many times, however, text values are not recognized as dates. For example:

= Date.FromText( "31122022" )   // ddMMyyyy returns an Error
= Date.FromText( "12312022" )   // MMddyyyy returns an Error
= Date.FromText( "20223112" )   // yyyyddMM returns an Error
= Date.FromText( "12-31-2022" ) // MM-dd-yyyy returns an Error
= Date.FromText( "2022-31-12" ) // yyyy-dd-MM returns an Error
= Date.FromText( "31*12*2022" ) // dd*MM*yyyy returns an Error
= Date.FromText( "31.*12.*22" ) // dd.*MM.*yy returns an Error
Failed date conversion

Manual Date Format

To correctly transform a text string to a date value, you can make use of the optional options parameter in the second argument. You can input a formatting string (within a record) to instruct the function which format the text value has. The examples that returned an error before, work with the below formatting instructions.

= Date.FromText( "31122022",   [Format="ddMMyyyy"] )   // Returns #date( 2022, 12, 31 )
= Date.FromText( "12312022",   [Format="MMddyyyy"] )   // Returns #date( 2022, 12, 31 )
= Date.FromText( "20223112",   [Format="yyyyddMM"] )   // Returns #date( 2022, 12, 31 )
= Date.FromText( "12-31-2022", [Format="MM-dd-yyyy"] ) // Returns #date( 2022, 12, 31 )
= Date.FromText( "2022-31-12", [Format="yyyy-dd-MM"] ) // Returns #date( 2022, 12, 31 )
= Date.FromText( "31*12*2022", [Format="dd*MM*yyyy"] ) // Returns #date( 2022, 12, 31 )
= Date.FromText( "31.*12.*22", [Format="dd.*MM.*yy"] ) // Returns #date( 2022, 12, 31 )
Custom formatting for date conversion

You can build your custom-format strings using any of the following strings:

FormatDescription31/12/202301/02/2003
%dSingle Digit Day (1-31)311
ddDouble Digit Day (01-31)3101
dddShort Weekday NameSunSat
ddddFull Weekday NameSundaySaturday
%MSingle Digit Month (1-12)122
MMDouble Digit Month (01-12)1202
MMMShort Month NameDecFeb
MMMMFull Month NameDecemberFebruary
%yYear (0-99)233
yyYear (00-99)2303
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.

Tip

You can also find these custom format strings for time, datetime, datetimezone and number values as described in this guide on formatting strings.

Provide Culture Code

All examples before work regardless of the language your machine has. Yet, sometimes a notation can be different depending on the culture. For example, when taking into account the month name.

In those cases, you can provide the options parameter with a culture. When omitting this culture, the function by default uses the culture of your machine.

So what are examples that are culture-dependent?

= Date.FromText( "30 Mrt 2022", [Format="dd MMM yyyy"] )
// Returns an error, PQ does not recognize Mrt as March

= Date.FromText( "30 Mrt 2022", [Format="dd MMM yyyy", Culture="nl-NL"] )
// Returns #date( 2022, 3, 30). Mrt = March in Dutch

= Date.FromText( "30 Maart 22", [Format="dd MMMM yy", Culture="nl-NL"] )
// Returns #date( 2022, 3, 30). Maart = March in Dutch

= Date.FromText( "30 juin 2022", [Format="dd MMM yyyy"] )
// Returns an error, PQ does not recognize juin as June

= Date.FromText( "30 juin 2022", [Format="dd MMM yyyy", Culture="fr-FR"] )
// Returns #date( 2022, 6, 30 ), juin = June in French

Conclusion

And there you go! You now have the skills to extract dates from text using Power Query’s Date.FromText function. Not only can you auto-extract dates, but you can also specify formats and even apply culture codes for more targeted results.

Are you interested in doing the reverse? Then check out this guide on formatting dates as text.

Tip

You can also use the techniques you’ve just learned to whip up a calendar in no time. You can use the methods described here to generate a calendar from scratch.

So, over to you: Have you put the Date.FromText function to work yet? Encountered any unique challenges or scenarios not covered here? Let me know in the comments and share your story. Or if you haven’t tried it yet, go ahead—give it a try and let me know how it went.

Happy querying!

Share on:
    • Hi Jok,

      Ordinal Numbers are a little tricky. Power Query does not recognize them as such. One approach to achieve your desired outcome is:

      let
        dateString = "1st January 2024",
        offset = Text.PositionOf( dateString, " "),
        cleanDateString = Text.RemoveRange( dateString, offset - 2, 2 ),
        myDate = Date.From( cleanDateString  )
      in 
        myDate
      Reply
  1. Hi Rick, thank you so much for your sharing.
    Hope you could help the below?
    I have the date time as below
    1/8/2023 3:19:00 AM
    1/8/2023 4:22:02 AM
    1/8/2023 4:22:03 AM
    1/8/2023 9:28:06 AM
    ….
    and want to convert into date.
    I’ve tried the below formula but still getting error

    = Table.AddColumn(
      #"Changed Type", 
      "Date", 
      each Date.FromText( [Resevation time],[Format="d\/m\/yyyy  hh\:mm\:ss AM/PM"] )
    )

    thank you.

    Reply
    • I also tried:

      Date.FromText([Resevation time],[Format= "%d/%m/yyyy hh:mm:ss AM/PM"]

      but still error
      Parameter.Error: We couldn’t convert the text value to date using the specified format. The format includes a time component.
      Details:
      Format=%d/%m/yyyy hh:mm:ss AM/PM

      hope you could help

      Reply
      • Hi!

        I know this can be quite confusing. The Date.FromText function does not support any time components as specified on this page: https://powerquery.how/time-totext/

        You could take multiple approaches.

        Either you can first turn the text into a datetime format with something like:

        DateTime.Date( 
          DateTime.FromText( 
            "1/8/2023 3:19:00 AM", 
            [Format = "d/M/yyyy %h:mm:ss tt"] 
            ) 
        )

        Alternatively, you could extract all characters before the first space in your string, and turn that string into a date.

        Date.FromText( 
            Text.BeforeDelimiter( "1/8/2023 3:19:00 AM", " " ),
            [Format = "M/d/yyyy"] 
        )

        When converting a date value to a text we can also insert arbitrary characters like:

         // Output: "January 31, 2024 will be on Wednesday"
        Date.ToText( 
          #date( 2024, 1, 31 ), 
          [ Format = "MMMM %d, yyyy' will be on' dddd" ] 
        )

        Unfortunately, the reverse does not work due to a bug in Power Query. That bug was reported a couple of weeks ago. Hope that helps!

        Reply
  2. Hi Rick,
    Hope you can help out here. I had a challenge at a job interview process where I was given a dataset with the date column in year & quarter and the column format as Time. I was unable to transform it into a proper Year-quarter.
    Part od the dataset below.
    Time
    201501
    201504
    201507
    201510

    Reply
    • Hi Herve. That’s an interesting challenge.

      You could try something like:

      let 
        value = 201504,
        textvalue = Text.From( value ),
        year =  Text.Start( textvalue, 4 ),
        month = Number.From( Text.End( textvalue, 2 ) ),
        quarter = Number.RoundAwayFromZero( month / 3 ),
        result = year & "-" & Text.From( quarter )
      in 
        result
      Reply
    • Hey Robert,

      That’s strange. For me everything seems to work. Could it be that your computer has a different language that requires a different notation of “yyyyMMdd”.

      If I had to guess your last name sounds german. Would for example “jjjjmmtt” work for you?

      Reply
  3. Hi Rick,
    Thanks for this article. Pity you don’t have the samples for download.
    With the Date.FromText function I am running into errors as soon as I specify the Format argument, after trying for a long time it looks to me like a bug or something regional settings related.
    I have a simple sample file I can send so you see the effect …
    Essentially I have date colums with Text like “20220726” and I try to convert it with … each Date.FromText([Date Text], [Format=”yyyyMMdd”]), to a date value, this combination is causing an error, where as … each Date.FromText([Date Text]), returns a valid date. Also other formats (you used) cause errors … as soon as i use the Format argument
    Any ideas?
    Servus
    Robert

    Reply
    • Hi Robert,

      Can you give this code a try? You can copy-paste it into the advanced editor of a query and it should show you a working example.

      let
        Source = Table.FromRows(
          Json.Document(
            Binary.Decompress(
              Binary.FromText(
                "RYy7DoAgDEV/pWHmdctAGPkAF+NG2DVxMCb+vx
                 SIJh16Tm9vKYo9MzhAVV1UgAEbUR1lETGvgg7s
                 vrAF2y/sods09B2RUnRwIwrZmhjAtOSV5ltrkZ
                 q/Z9sfTZHydR8nDVtf", 
                BinaryEncoding.Base64
              ), 
              Compression.Deflate
            )
          ), 
          let
            _t = ((type nullable text) meta [Serialized.Text = true])
          in
            type table [#"Date Text" = _t]
        ), 
        #"Added Custom" = Table.AddColumn(Source, "Date", 
                          each Date.FromText([Date Text]), type date), 
        #"Added Custom1" = Table.AddColumn(
          #"Added Custom", 
          "Date Format", 
          each Date.FromText([Date Text], [Format = "yyyyMMdd"]), 
          type date
        )
      in
        #"Added Custom1"

      Cheers,
      Rick

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.