Converting string reprentation of date to Date format

[underhill]

Hi,

I have a report that issues a date in text format to a spdsheet. e.g
the cell content will say 20040501 which = 01/05/2004. I need to get
that into date format to sort and calculate etc. I thought I cd
formulate as follows:

=right(a1,4) & "/" mid(a1,5,7) & left(a1,2) (or something like that!)

and then convert the data type/format of the cell. This isnt working
though. I have also tried using =text(a2,"dd/mm/yyyy") to no avail.

I hope I'm on the right track (although I am in no way an "advanced
user" and prob have no clue!)

Any ideas?

 

[Dave Peterson]

I think the quickest way to convert these things (if they're all in a single
column) is to do:

Data|Text to columns
Fixed width
remove any lines that excel guessed
choose ymd (or ydm)
(I'm not sure if your date is May 1, 2004 or Jan 5, 2004)

and put it right back where you found it.

 

[underhill]

David,

Your reponse is exactly what I needed to know. Thanks v. much

:

 

[Ron Rosenfeld]

Hi,

I have a report that issues a date in text format to a spdsheet. e.g
the cell content will say 20040501 which = 01/05/2004. I need to get
that into date format to sort and calculate etc. I thought I cd
formulate as follows:

=right(a1,4) & "/" mid(a1,5,7) & left(a1,2) (or something like that!)

and then convert the data type/format of the cell. This isnt working
though. I have also tried using =text(a2,"dd/mm/yyyy") to no avail.

I hope I'm on the right track (although I am in no way an "advanced
user" and prob have no clue!)

Any ideas?

Here's a formula that should work:

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

If it does not, there may be extra characters in the date string.

So you might have to substitute for A1 in the above formula something like:

=TRIM(A1)

or (especially if it is coming from the Web):

=SUBSTITUTE(TRIM(A1),CHAR(160),"")


--ron