Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default convert second to date and time

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default convert second to date and time

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)


"--== Alain ==--" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 620
Default convert second to date and time

=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.
--
David Biddulph

"--== Alain ==--" wrote in message
...
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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default convert second to date and time

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.

Bob Phillips wrote:
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?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default convert second to date and time

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)


"--== Alain ==--" wrote in message
...
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.

Bob Phillips wrote:
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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help: convert seconds to date and time misty1 Excel Discussion (Misc queries) 2 November 26th 05 05:12 AM
Using VLOOKUP with a Date and Time Charles Excel Discussion (Misc queries) 4 September 20th 05 06:38 PM
convert interval to various separate date , time, hr, minutes Todd F. Excel Worksheet Functions 4 July 12th 05 07:25 PM
Convert date to length of time in months from set date MJUK Excel Worksheet Functions 1 March 19th 05 07:31 PM
how do I convert a date and time column to a time column thdorsky2 Excel Worksheet Functions 1 March 4th 05 09:49 PM


All times are GMT +1. The time now is 04:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"