How to decode integer timestamps?

[Ron West]

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!

 

[Glenn]

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

 

[Ron West]

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?

 

[John C]

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?

 

[Ron West]

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

 

[Glenn]

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?

 

[John C]

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)),"yyyy/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

 

[T. Valko]

Assuming 20081119 = yyyymmdd

Try it like this:

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

Format as yyyy/mm/dd

 

[David Biddulph]

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