Need help with converting CUSTOM format/TEXT format to DATE format

[Deo Cleto]

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.

 

[Eduardo]

Hi,
what information do you have in that cell, if you have a date when selecting
another format it should work, check the number in the cell before formating
it, the number give below when formating as date it give you 06/27/4670 so
something is wrong with the number you are importing into that cell

 

[Deo Cleto]

what information do you have in that cell
ANS: Originally it was entered 01-01-1901, in CUSTOM format as 00-00-0000
so I changed it to DATE format but the system changed it to 06/27/4670.
Again, from the CUSTOM format 01-01-1901, I changed it to TEXT format and it
changed to 1011901. I really need to changed it to DATE format as
mm/dd/yyyy, is there any worksheet function that can convert CUSTOM or TEXT
formt to DATE format.

Thanks for your quick response.

 

[Eduardo]

Hi Deo,
try

=DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(RIGHT(A1,2)))

I assume that the number is in cell A1 change it to fit your needs

if this helps please click yes, thanks

 

[Ron Rosenfeld]

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

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

 

[Deo Cleto]

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'));

 

[Deo Cleto]

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'));