| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help: convert seconds to date and time | Excel Discussion (Misc queries) | |||
| Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
| convert interval to various separate date , time, hr, minutes | Excel Worksheet Functions | |||
| Convert date to length of time in months from set date | Excel Worksheet Functions | |||
| how do I convert a date and time column to a time column | Excel Worksheet Functions | |||
Linear Mode
