How to convert string to date format in Power Query

Question

I am trying to convert a column in a csv file that contains the following datetime format.

12 mar 2016 10:27:47 fm

But when I change type to DateTime I get this error:

Error: Could not parse the input provided to a DateTime value

I have tried converting using different locale as well with no success.

How can I convert it to a correct datetime format?

Carl Walsh · Accepted Answer · 2016-03-16 05:58:19Z

1

Our date/time parser for locales like Swedish where you'd use "fm" for "am" only allows 24-hour time. Sorry that you ran into this!

Alejandro's fix would look like:

DateTime.FromText(Text.Replace(Text.Replace("12 mar 2016 10:27:47 fm", " fm", " am"), " em", " pm"))

answered Mar 16, 2016 at 5:58

Carl Walsh

7,2173 gold badges53 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MrDark Over a year ago

It is a mix of the US locale date format "12 mar 2016 10:27:47 pm" but with the Swedish abbreviations for both am/pm and the month names. So I had to replace "maj" with "may" and "okt" with "oct" as well. Problem solved! Thanks for your help!

Alejandro Lopez-Lago - MSFT · Accepted Answer · 2016-03-14 19:00:55Z

1

Should the fm on that line be am or pm?

You can work around it by replacing fm in the text with am or pm using Text.Replace and then using that text in DateTime.FromText.

answered Mar 14, 2016 at 19:00

Alejandro Lopez-Lago - MSFT

4,18422 silver badges25 bronze badges

Collectives™ on Stack Overflow

How to convert string to date format in Power Query

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related