Need help with converting CUSTOM format/TEXT format to DATE fo
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'));
"Eduardo" wrote:
Hi Deo,
try
=DATE(VALUE(LEFT(A1,1)),VALUE(MID(A1,2,2)),VALUE(R IGHT(A1,2)))
I assume that the number is in cell A1 change it to fit your needs
if this helps please click yes, thanks
"Deo Cleto" wrote:
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" wrote:
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" 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.
|