Convert to Date

L

Leif

Hi,
From another system we get files with Date where date is like 1021101 which
should be 2002/11/01 and fields with 941101 should be 1994/11/01.

I have tried to add 19000000 to the number and that gives me the correct
20021101 but how can I get it formatted as date?

Regards
Leif
 
A

Andy B

Hi

Once you have done your bit (!!) you can use Data / Text to columns. You can
then tell Excel that it's a date in which format and you're done!
 
T

Tornados

Use the Date() function:

a1 says: 941101

Date(left(a1,2),mid(a1,3,2),right(a1,2))

will give you what you want....

it might be4 that you have to add : 19& left(a1,2) if you are belo
2000 and 20 & left(a1,2) when it's later than 20...

Regards
 
L

Leif

Thanks!!

Leif
Andy B said:
Hi

Once you have done your bit (!!) you can use Data / Text to columns. You can
then tell Excel that it's a date in which format and you're done!
Click to expand...
 
R

Ron Rosenfeld

Hi,
From another system we get files with Date where date is like 1021101 which
should be 2002/11/01 and fields with 941101 should be 1994/11/01.

I have tried to add 19000000 to the number and that gives me the correct
20021101 but how can I get it formatted as date?

Regards
Leif
Click to expand...


With the date in A1:

=DATE(INT(A1/10^4)+1900,INT(MOD(A1,10^4)/100),MOD(A1,100))


--ron
 
A

Anderson Lee

You could download the third party tool: AddinTools Assist from
http://www.addintools.com. It includes several functions. One of these
functions is convert data of selected cells to text/number/date-time type.
It can convert data of all formats to text type.
It can recognize these formats as following and convert to date-time type:
2003.1.2, 2003.1.2 11:12:13:14PM, 2003-1-2, 2003-1-2 11:12:13.14PM,
20030102, 20030102 11:12:13:14PM, and locale format on your computer, and so
on …
 
Top