ExcelBanter - View Single Post - Need help with converting CUSTOM format/TEXT format to DATE format

Ron Rosenfeld

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