How to decode integer timestamps?
 

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!

How to decode integer timestamps?
 

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

How to decode integer timestamps?
 

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

How to decode integer timestamps?
 

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

How to decode integer timestamps?
 

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

How to decode integer timestamps?
 

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!

How to decode integer timestamps?
 

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

How to decode integer timestamps?
 

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

How to decode integer timestamps?
 

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