[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Excel query to convert date format

Posted on 2011-02-19
3
Medium Priority
?
1,066 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:RTsal
3 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 34933319
If you can get it to yyyy-mm-dd excel will recognize it.
0
 
LVL 18

Assisted Solution

by:mirtheil
mirtheil earned 1000 total points
ID: 34933353
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 34935814
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question