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

 

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