convert second to date and time

  • Thread starter --== Alain ==--
  • Start date
A

--== Alain ==--

Hi,

i've got some exported log from unix platform.
in those logs, time and date is written as lapsed second since 01.01.1970.

How can i extract date and time from this lapsed second since 01.01.1970.

e.g : 1165391099.763

thanks a lot,

Al.
 
B

Bob Phillips

This gets the number of years assuming that value is in A1

=DATEDIF(--"1970-01-01",--"1970-01-01"+A1/24/60/60,"Y")

How do you want the answer?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
D

David Biddulph

=DATE(1970,1,1)+A1/24/60/60 and format as dd.mm.yyyy hh:mm:ss.000 (to show
as 06.12.2006 07:44:59.763) or however you want it displayed.
 
A

--== Alain ==--

Thx Bob,

in fact i would like to have in E1 something like a date e.g : 16.02.2006

and in F1 something like a time e.g : 14:12:27

Al.
 
B

Bob Phillips

Ok.

E1: ="1970-01-01"+INT(A1/24/60/60)
F1: =MOD("1970-01-01"+A1/24/60/60,1)

Maybe best to put 01/01/1970 in a cell, such as D1 and use

E1: = D1+INT(A1/24/60/60)
F1: = =MOD(D1+A1/24/60/60,1)

Format E1 as date, and F1 as time.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Top