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.