How to change format from 2/14/23 to 2/14/2023 in Query Editor?

[Newbie22]

Hi All,

I have a data source that has a date&time column but the year is not complete. It's in text format and something like this "2/14/23 08:00:00 AM"

Is there anyway to change that into date/time column in Query Editor? Currently it's in text format but when I change it to date/time format It's giving an error because it does not recognize the year. I need to change the year (23) to a complete year (2023) but I don't know how to.

Hope I make sense. Thank you so much for your help.

[ronrsnfld · Accepted solution]

Something strange about your data or your system

1. On my editor, those dates are automatically converted to the date of the current year.
2. Power Query should be able to recognize years down to the year one (1) and should have no problem recognizing the year "23"
3. Perhaps your system is not recognizing the "/" delimiter for dates

Suggest you try Changing Type with Locale

#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start Time", type datetime}}, "en-US")

or (from right-click on column menu:

[Newbie22]

Thank you @ronrsnfld. It worked! 😉

[AlexisOlson]

Odd. My Power BI works fine with both a type conversion and DateTime.FromText.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYy1LfUNzJWsLAyNbQyNlZw9IWIGukbmegbGSkYGloZWQKRQgBQIhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Time" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each DateTime.FromText([Start Time])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Start Time", type datetime}})
in
    #"Changed Type"

Start:

Result:

[jaweher899]

Here's how to do it:

1. Select the column that contains the date/time values.

2. Click on the "Add Column" tab in the ribbon, and select "Custom Column" from the dropdown menu.

3. In the formula bar, use the following formula to extract the year from the text:

   = DateTime.FromText("2/14/23 08:00:00 AM") + #duration(365*(Text.Range([Date&Time], Len([Date&Time])-1, 2)-1900),0,0,0)

   This formula uses the Text.Range function to extract the last two characters (which represent the year) from the text value, and then converts it to a datetime value by adding a duration to set the year.

4. Click "OK" to create the new column.

5. If necessary, you can then delete the original text column and rename the new column to match the original column name.

Note: The above formula assumes that the year is represented by the last two characters in the text format. If the year is represented differently, you may need to adjust the formula accordingly.

[Newbie22]

Thank you for your fast reply @jaweher899 

But I'm getting this error when I follow your code.

Did I do something wrong?

[jaweher899]

please replace len by length

[Newbie22]

Still not working. ☹️ I don't think there's an M code len or length 

[jaweher899]

please check https://learn.microsoft.com/fr-fr/powerquery-m/text-length Length capital case

[Newbie22]

I have a new error.

When I click the error it says: "Count Argument is out of range".