Excel query to convert date format

I have an excel query that returns data from my Pervasive db. However the date format is not recognized as a date by excel. How can I convert the date format so that it is recognized by excel. I would prefer to have it converted with the query statement but if that can't be done I would have to convert it once it is returned to excel.
Here is the date format it returns:
YYYYMMDD
so Feb 19 2011 is shown as 20110219
RTsalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Aaron TomoskySD-WAN SimplifiedCommented:
If you can get it to yyyy-mm-dd excel will recognize it.
0
 
mirtheilCommented:
From a Pervasive perspective, that doesn't seem like a data field because PSQL keeps the format as yyyy-mm-dd.  You can convert it to that format in a PSQL query with something like:
select left(f1, 4) + '-' + substring(f1,4,2) + '-' + right(f1,2) from table
You can even convert that to a Date data type with the following:
select CONVERT (left(f1, 4) + '-' + substring(f1,4,2) + '-' + right(f1,2), SQL_DATE) from table
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

You can use several techniques in Excel to convert this to a date.

Method 1:

Select the column with the dates, then click Data > Text to Columns > Fixed Width. Click Next and in the "Column Data Format" area select Date and use the dropdown to set it to YMD. Then click Finish.


Method 2:

Use a helper column with the formula (assuming your "date" values are in column A, starting in row 1)

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Copy the formula down all rows.

Copy the results of the helper column and use Paste Special > Values to paste them over the original data, then delete the helper column.

cheers, teylyn

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.