Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
decode | Excel Discussion (Misc queries) | |||
charts with Timestamps - only date part taken into account | Charts and Charting in Excel | |||
how to find time difference between timestamps to a millisecond? | New Users to Excel | |||
serial number decode | Excel Worksheet Functions | |||
timestamps in excel | Excel Discussion (Misc queries) |