Converting negative date values

C

Carlos

I imported some data from a delimited file where one of
the columns represented date data. When importing the data
to Excel, I requested from the program to assign a date
data type to the date column and completed the data
import. Unfortunately, the import process assigned a
general data type to the date column and the values are
represented like in 20040605 for June 5, 2004. Excel sees
20040605 as a number but when trying to format the cell to
a date, I get a string of ####### indicating that the date
value is a negative date.

Any suggestions on how to convert this data into a date
format?

Thank you.
 
N

Norman Jones

Hi Carlos,

Select date cells
Data | Text to Columns | Next | Next
Check the date option
Select YMD

Format the resultant serial dates as you wish
 
G

Guest

Thank you very much. It worked great!

-----Original Message-----
Hi Carlos,

Select date cells
Data | Text to Columns | Next | Next
Check the date option
Select YMD

Format the resultant serial dates as you wish


---
Regards,
Norman





.
Click to expand...
 
A

Anderson Lee

You could download the third party tool: AddinTools Assist from
http://www.addintools.com. It include several functions. One of these
functions is convert data of cells to text/number/date-time type.
It can recognize these formats as following and convert to date-time type:
2003.1.2, 2003.1.2 11:12:13:14PM, 2003-1-2, 2003-1-2 11:12:13.14PM,
20030102, 20030102 11:12:13:14PM, and locale format on your computer, and so
on …
 
Top