Apagada docs

Aprendiendo a programar el pasado

Herramientas de usuario

Herramientas del sitio


en:unix_or_sqlite_date_to_excel

Unix or sqlite date to excel

Unix > Excel

Unix counts time as seconds from 1/1/1970 0:0:0, while excel counts time as days from 1/1/1900.

So you can simply use this formula to convert the seconds to days and add the start date:

=[unix time]/(60*60*24)+DATE(1970,1,1)

Since unix stores UTC 0 (GMT) universally valid timestams, you'd probably need to add 1 hour (1/24 day) to get a Spanish/French/German date, or substract 5 hours (-5/24 days) if you live on eastern USA. You'd also need to add the DST summer time, which is more problematic (I know of no excel formula to get “first sunday after october”).

You will get an excel date serial. To show it as date you can change cell format to “date”, or use the TEXT function:

=TEXT([unix time]/(60*60*24)+DATE(1970,1,1) , “mm-dd-yyyy”)

Sqlite > Excel

Sqlite timestamp fields use milliseconds, so you have to modify the formula:

=[sqlite timestamp in milliseconds]/(60*60*24*1000)+DATE(1970,1,1)

You'd also need to adjust the returned value to your current timezone.

Este sitio web utiliza cookies. Al utilizar el sitio web, usted acepta almacenar cookies en su computadora. También reconoce que ha leído y entendido nuestra Política de privacidad. Si no está de acuerdo abandone el sitio web.Más información
en/unix_or_sqlite_date_to_excel.txt · Última modificación: 2017/09/24 06:04 por nepenthes