Problem converting EU dates with Time to US Date Format

[polarisking]

I'm having difficulty converting EU dates (DMY) that include a time component into US (MDY). See the last 2 rows (36 and 37) of the attached file. I want them to resolve to 06/25/2021, but they're staying at 25/06/2021.

I've tried a number of techniques (Import wizard), Text to Columns, etc. Nothing works. HoIf I remove the time element, it all works.

Any ideas? Thanks in advance.

 

[p45cal]

Power Query will correctly handle known date type formats by specifying a locale:

let
    Source = Csv.Document(File.Contents("C:\Users\Public\Test for EU Date Time Conversion for Chandoo.txt")),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"EUDate_and_Time", type datetime}}, "en-GB")
in
    #"Changed Type with Locale"

 

[AlanSidman]

Here is a Power Query Solution for you

let
    Source = Csv.Document(File.Contents("C:\Users\alans\Downloads\Test for EU Date Time Conversion for Chandoo.txt"),[Delimiter=":", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "EUDate_and_Time")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Column1.2", "Column2", "Column3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Time"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Merged Columns", "Column1.1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1.1", "Column1.1.2", "Column1.1.3"}),
    #"Merged Columns1" = Table.CombineColumns(#"Split Column by Delimiter1",{"Column1.1.2", "Column1.1.1", "Column1.1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Merged", "Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"m/d/y h:m:s")
in
    #"Merged Columns2"

 

[polarisking]

Thanks to both of you for the Power Query solution. Right now, I'm processing the file using VBA:

>>> use code - tags <<<

With wsExtract.QueryTables.Add(Connection:="TEXT;" & strFileToOpen, Destination:=wsExtract.Range("A1"))
        .FieldNames = True
        .TextFileParseType = xlDelimited
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 1, 2, 1, 4, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 4)
        .Refresh
End With

The 4 value denotes DMY and for positions 5, and 6 the conversion is perfect. The last column is the one I peeled off in the test file.

Hard to believe something this straightforward EU to US is getting confused merely by the addition of the time value.

Oh well . . .