Forum Discussion
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
I have managed to change the date format of the "Last Contact date" column within Modelling by using that formating by doing a search on the forum,
ButI would like the correct date to appear in the "Last Contact merged" column as all the others do currently it comes up with an error.
Thanks in advance
8 Replies
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.
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.
