Assuming 20081119 = yyyymmdd

Try it like this:

=--TEXT(A1,"0000\/00\/00")

Format as yyyy/mm/dd

--
Biff
Microsoft Excel MVP


"John C" <johnc@stateofdenial wrote in message
...
If you want, you could do the one of the following as well:
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"yy yy/mm/dd")
or
=REPLACE(REPLACE(A1,7,0,"/"),5,0,"/")
Don't forget to mark question as answered by clicking the YES box below
(be
sure to give credit to Glenn for his original response).

Glad it's working :)
--
** John C **

"Ron West" wrote:

OK - I was wrong - it does work - but I have to apply a Custom cell
format
"YYYY/MM/DD" to display it correctly

"John C" wrote:

I am trying to figure out exactly what you are trying to accomplish.
You have
data of 20081119 in a cell, you need the date formatted as 2008/11/19
in
another cell. Why is it that Glenn's formula doesn't work?
--
** John C **

"Ron West" wrote:

No, all that does is display the date by hacking it into pieces and
sticking
them together again.

How would that method be able to supply the data for a
correctly-scaled date
axis on a (x,y) graph?


"Glenn" wrote:

Ron West wrote:
I've been asked to find a simple way of decoding a numeric
(integer)
timestamp (representing date only) into native Excel date format,
so that it
can be displayed in the "YYYY/MM/DD" format AND also be used as
data for one
of the axes of a graph on the same sheet.

For example, I need to take a column of numeric timestamps in the
form
20081119 (integer datatype) and convert them to Excel
date-numbers like 39772
so that when I display the resulting column using the
"YYYY/MM/DD" format it
comes back out like "2008/11/19", etc.

Obviously, I can do it using a lot of fiddly coding to extract
the date
parts, but I can't be the first person to want to do this! Are
there any
native TimeStamp processing functions in Excel that I've missed?

Thanks!


=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))