Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
timestamps in excel
I am importing data from a logging application
(as in data, not trees), and the first entry of each row is a timestamp--as in the number of seconds since the epoch, which is January 1, 1970 or something like that. The problem is, I cannot see how to make this a date in Excel. All of the standard date and time options in "Format Cells" do not know how to convert this--that is, it puts "#########" in the cells. Any ideas? Chuck. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
timestamps in excel
Excel dates work on a similar principal, but they measure day in terms
of a reference date of 31st December 1899 (day 0), so you will have to add the number of days that your reference date represents in Excel. Enter 1/1/70 into a cell and format it as a number - this will show you that it represents 25569 days, so this is what you will have to add to your elapsed days to make it into Excel dates. Times are represented as fractions of a 24 hour day, so to convert your seconds you will have to divide by 60, by 60 again and by 24. So, if your timestamp is in cell A1, this formula in B1 should do the trick: =A1/60/60/24 + 25569 Format the cell as Date in the appropriate style. Hope this helps. Pete |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
timestamps in excel
I should have been more specific. By "date", I mean date and time.
Thus, I want it to display something like "January 23, 2006 1:23:45 am". In other words, I am not just interested in the date, but the time as well. Chuck. "Pete" wrote: Excel dates work on a similar principal, but they measure day in terms of a reference date of 31st December 1899 (day 0), so you will have to add the number of days that your reference date represents in Excel. Enter 1/1/70 into a cell and format it as a number - this will show you that it represents 25569 days, so this is what you will have to add to your elapsed days to make it into Excel dates. Times are represented as fractions of a 24 hour day, so to convert your seconds you will have to divide by 60, by 60 again and by 24. So, if your timestamp is in cell A1, this formula in B1 should do the trick: =A1/60/60/24 + 25569 Format the cell as Date in the appropriate style. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
timestamps in excel
What I said will still work - just use a custom format like mmmm dd,
yyyy h:mm:ss (you'll see some examples if you scroll down the list). It sometimes helps to split these out though, so you can use =INT(B1) to get the date (only) part and =B1-INT(B1) to get the time part, then format each as appropriate. Hope this helps. Pete |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
timestamps in excel
See
Converting Unix timestamps to XL date/times http://mcgimpsey.com/excel/udfs/unixtoxltime.html In article , Chuck Cusack wrote: I am importing data from a logging application (as in data, not trees), and the first entry of each row is a timestamp--as in the number of seconds since the epoch, which is January 1, 1970 or something like that. The problem is, I cannot see how to make this a date in Excel. All of the standard date and time options in "Format Cells" do not know how to convert this--that is, it puts "#########" in the cells. Any ideas? Chuck. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |