timestamps in excel

[Guest]

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.

 

[Pete]

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

 

[Guest]

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]

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

 

[JE McGimpsey]

See

Converting Unix timestamps to XL date/times
http://mcgimpsey.com/excel/udfs/unixtoxltime.html