Convert UK to US date format in CSV

Welcome Forums General PowerShell Q&A Convert UK to US date format in CSV

This topic contains 5 replies, has 4 voices, and was last updated by

3 years, 10 months ago.

  • Author
  • #21378

    Points: 0
    Rank: Member


    I have a CSV file with 10 columns (the last column is the date)
    Currently the date is DD/MM/YYYY and I need to change this to the US format which is MM/DD/YYYY]

    What's the best way to do this? Should I slice the day month and year into a variable and then reorder it or is there an easier way?


  • #21382

    Points: 0
    Rank: Member
    Get-Date -Format dd/mm/yyyy

    And just because that answer seems incredibly short – there's actually an article linked from the Get-Date help on TechNet that gives you a full list of format types you can use. Enjoy!

  • #21383

    Points: 0
    Rank: Member

    Thanks Will,
    Sorry I should have probably been clearer – the dates in the csv file are different dates on each line and none of them are today's date so I need to get the date already in the file and then convert, not just put today's date 🙂

  • #21384

    Points: 0
    Rank: Member

    Unfortunately there isn't an easy way to do this. Your CSV file holds the date as a string. You need to convert that to a date. If you try this
    £> $sduk = '25/12/2014'
    £> $d = [datetime]$sduk
    Cannot convert value "25/12/2014" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
    At line:1 char:1
    + $d = [datetime]$sduk
    + ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvalidCastParseTargetInvocationWithFormatProvider

    where the date is 25 December 2014 in UK format it will fail.

    .NET expects the string in US format

    £> $sdus = '12/25/2014'
    £> $d = [datetime]$sdus
    £> $d

    25 December 2014 00:00:00

    You could do something like this

    £> $sd = '25/12/2014' -split '/'
    £> $sd
    £> $d = Get-Date -Day $sd[0] -Month $sd[0] -Year $sd[2]
    £> $d

    25 December 2014 18:25:56

  • #21386

    Points: 0
    Rank: Member

    Casting a string to a datetime assumes invariant (basically US) format, but calling [datetime]::Parse() allows you to specify a culture (as does the ToString() method on datetime objects). For example:

    $ukString = '25/12/2014'
    $ukCulture = [Globalization.CultureInfo]'en-GB'
    $usCulture = [Globalization.CultureInfo]'en-US'
    $datetime = [datetime]::Parse($ukString, $ukCulture)
    $usString = $datetime.ToString('d', $usCulture)
  • #21394

    Points: 0
    Rank: Member

    Whoops! I totally misread your original post. My bad Nick. 🙂

The topic ‘Convert UK to US date format in CSV’ is closed to new replies.