I'm sorry I mispost it to Eduardo, this is the resolution that works except
that it included the time stamps. Thanks.

Can you get rid of the timestamps, when I imported this file to TOAD it
included the timestamp:

Insert into DJIMENEZ.TEMP_INSD_BDAY
(POLICY_SEARCH_NBR, LAST_NAME, FIRST_NAME, MIDDLE_NAME, SUFFIX,
SSN, ADDRESS, CITY, STATE, ZIP_CODE,
PO_BOX_FLAG, DOB, DATE_OF_BIRTH)
Values
('3-HOC-1-1144198 ', 'OLSZEWSKI ', 'JOYCE ', ' ', ' ',
' ', '1502 STOCKBRIDGE DRIVE ', 'SAN JOSE ', 'CA ', '95130',
'0', '1011901', TO_DATE('01/01/1901 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));



"Ron Rosenfeld" wrote:

On Fri, 29 May 2009 10:13:02 -0700, Deo Cleto <Deo
wrote:

A column in an excel file was formatted in CUSTOM format like 00-00-0000.
Whenever I changed the format to DATE (ex. 01-01-1901), it is changing to
06/27/4670 which is wrong. I changed the format to TEXT and it results to
1011901. I need a date format as mm/dd/yyyy so I can import this excel file
with the correct date format. Please help. Thanks.

Well, it appears as if that data was entered as an eight digit value.

You will not be able to get an Excel date out of that with just formatting.
First you have to turn that value into a real Excel date. Then you can format
it as you like.

Excel dates, depending on the date system you are using, start with either
1/1/1900 or 1/1/1904 and count serially upwards.

So you will need a "helper column" to convert the value to a "real" date.

One formula that should work, assuming your date-like entry is in A1, is:

=DATE(MOD(A1,10^4),INT(A1/10^6),INT(MOD(A1,10^6)/10^4))

--ron