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,

 

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