• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem converting EU dates with Time to US Date Format

polarisking

Member
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.
 

Attachments

p45cal

Well-Known Member
Power Query will correctly handle known date type formats by specifying a locale:
Code:
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

Well-Known Member
Here is a Power Query Solution for you

Code:
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

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

>>> use code - tags <<<
Code:
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 . . .
 
Last edited by a moderator:
Top