Converting string reprentation of date to Date format

  • Thread starter Thread starter underhill
  • Start date Start date
U

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?

:)
 
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.
 
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?
Click to expand...

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
 
Back
Top