Forum Discussion

Nina_SC's avatar
Nina_SC
Copper Contributor
Jun 07, 2019
Solved

Convert Text to Date Format

Hello everyone,   I'm just an excel newbie and I've been finding ways on how to convert a date text string to date format. I find it peculiar that after transferring all my CSV files through a flas...
excel
  • Berndvbatanker's avatar
    Berndvbatanker
    Jun 07, 2019

    Nina_SC 

    Hi Nina,

    you can use this macro, see attached file to convert dates. It converts all dates in selection to real date.

     

    Sub KonvertDate()
    Dim rngCell As Range

    For Each rngCell In Selection
    rngCell.Value = CDate(rngCell.Value)
    Next rngCell

    End Sub

     

    Best regards from germany

    Bernd

    http://www.vba-tanker.com

Nina_SC's avatar
Nina_SC
Copper Contributor
Jul 14, 2019

Wyn Hopkins Hi wyn, thank you so much for your suggestion. I tried applying it and used power query in power bi -- and after changing the locale still nothing has changed. The error was still there. I don't know where the error is coming from but would you mind taking a look at my file? It would mean a lot and thank you so much.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 14, 2019

Nina_SC 

 

With Power Query click Data->From Table/Range (it'll be converted to table), Transform, in Power Query editor select all 3 columns, and from right click menu select Change Type->Using locale. Here Date and English (United States). Land the result back to the sheet.

Generate script is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {
        {"Created Time", type datetime},
        {"Last Update Time", type datetime},
        {"Close Time", type datetime}}
    , "en-US")
in
    #"Changed Type with Locale"

Please see Sheet2 in attached.

Resources