Forum Discussion
Changing Date Format
Hello,
I have extracted a CSV from a work system and there is a date column formatted as "mm/dd/yyyy hh:mm:ss: AM/PM". I'd like to have it formatted as "yyyy-mm-dd hh:mm". How do I do that?
Changing from the below:
To this:
Thanks in advance for your help!
Rather than reading the file into Excel directly, read it into PowerQuery using a US locale
let Source = Csv.Document(File.Contents("C:\Users\Peter\OneDrive\test.csv"),[Delimiter=",", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"ScheduleStartTimeStamp", type datetime}}, "en-US") in #"Changed Type with Locale"[here the data is loaded as text then converted to datetime using the US locale]
Once the correct dates are loaded to Excel number formatting can be used to display them using the international format you require.
4 Ways to Fix Date Errors in Power Query + Locale & Regional Settings - Excel Campus
1 Reply
Rather than reading the file into Excel directly, read it into PowerQuery using a US locale
let Source = Csv.Document(File.Contents("C:\Users\Peter\OneDrive\test.csv"),[Delimiter=",", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"ScheduleStartTimeStamp", type datetime}}, "en-US") in #"Changed Type with Locale"[here the data is loaded as text then converted to datetime using the US locale]
Once the correct dates are loaded to Excel number formatting can be used to display them using the international format you require.
4 Ways to Fix Date Errors in Power Query + Locale & Regional Settings - Excel Campus