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