Forum Discussion

LillyD's avatar
LillyD
Frequent Visitor
8 years ago

Change Date format in Query Editor

Hi,    I am trying to change the date format of a column in query editor from the AM / PM (highlight) values shown below.       If i change the column to Date/Time it errors as below    ...
General Comment
Need Help
Tutorial Requests
gooranga1's avatar
gooranga1
Power Participant
8 years ago

Hi LillyD,

 

What is being used to get that data in the first place as it seems to be mixed formats not one format for date. If you have access to the query that is pulling that data I would say it would be easier to get everything in one format there.

 

 

  • LillyD's avatar
    LillyD
    Frequent Visitor
    8 years ago

    Hi gooranga1 

    Yes I tried that, but the format didnt change in the xls spread sheet when I corrected it.  It actually changed the correct formating and lefts the incorrect format the way it is.

     

    In the spreasheet I have tried to copy the correcttly formated cell (dd/mm/yyyy hh:mm) using the format painter, and even though it updates the format of the cell the cell does not update.

     

    • gooranga1's avatar
      gooranga1
      Power Participant
      8 years ago

      Hi LillyD

       

      Mmm with date formats like that in excel I'm afraid you are in a world of pain.

       

      Using a couple of your examples you will have to modify those dodgy AM/PM formatted date strings in excel first and create your own new date column that you have calculated manually.

       

       

      Column would be your new column.

       

      Cell B2

      =TRIM(LEFT(A2,LEN(A2)-11))

      Cell C2

      =TIMEVALUE(RIGHT(A2,11))

      Cell D2

      =IF(ISNUMBER(B2),VALUE(TEXT(B2,"mm/dd/yyyy")),DATE(RIGHT(B2,2)+2000,LEFT(B2,FIND("/",B2)-1),MID(LEFT(B2,FIND("/",B2,4)-1),FIND("/",B2)+1,LEN(B2))))

      Cell E2

      =IF(ISERROR(TEXT(D2,"dd/mm/yyyy") & " " & TEXT(C2,"HH:mm:ss")),A2,TEXT(D2,"dd/mm/yyyy") & " " & TEXT(C2,"HH:mm:ss"))

      Copy those down and you should have a new 'date' column you can use.

       

       

       

      • LillyD's avatar
        LillyD
        Frequent Visitor
        8 years ago

        Hi, gooranga1,

         

        Great thanks for that I am getting there! but...

         

        The existing dates now show as null in Power bi from the orginal column, as they didnt transpose in Excel

         

         

        Power BI

         

        Any idea how i can combine the two columns please (sorry but all this is new to me), I trying to do as little manual work as possible in Excel as this will be an ongoing process for the migration.