Tabla de Contenidos

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.