Forum Discussion

mcp_excel04's avatar
mcp_excel04
Copper Contributor
Apr 02, 2024
Solved

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!

  • mcp_excel04 

    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

  • mcp_excel04 

    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

Resources