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!

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))

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))

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))

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))

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))

The formula turns a number, formatted to your specification, into an Excel date.
It should work just fine for a graph.


Did you try it?



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:


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


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!

.... or you might get away with =--TEXT(A2,"0000\-00\-00") and formatting as
date.
--
David Biddulph

"Glenn" wrote in message
...
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))