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

    • gooranga1's avatar
      gooranga1
      Power Participant
      8 years ago

      hi LillyD

       

      It doesn't look like you have copied the formula to all fields. I see there are filters on your spreadsheet clear all filters and make sure all the formulas are copied to all the cells, for valid dates and and AM/PM dates.

      • LillyD's avatar
        LillyD
        Frequent Visitor
        8 years ago

        Hi stil no luck I am afriad, here is the formala for row 5

         

        Column AE =TRIM(LEFT(AA5,LEN(AA5)-11))

        Column AF = TIMEVALUE(RIGHT(AA5,11))

         

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

         

        Column AH = IF(ISERROR(TEXT(AG5,"dd/mm/yyyy") & " " & TEXT(AF5,"HH:mm:ss")),AA5,TEXT(AG5,"dd/mm/yyyy") & " " & TEXT(AF5,"HH:mm:ss"))

         

         

        I have notice Cell in Col AA Row 2 is formatted as general text while cell in Col AA Row 5 is formated as a "Date / Tiime", but when converted to General it returns a result of 43289.6374