unix time to spreadsheet time
In Unix and Linux, times are stored as seconds since the Unix Epoc, 1970-01-01 00:00:00. Spreadsheets often use a different Epoc and they tend to store days instead of seconds.
When I import timestamped data that came from Unix land, I have to compensate for those differences.
First, I format two cells to YYYY-MM-DD HH:MM:SS and entering “0” as the cell data of the first. It shows me the epoc this program uses.
Next, I put the Unix Epoc in the second cell as “1970-01-01 00:00:00” and that’s what I see in that cell. Then I remove the DATE formatting from that cell and I’ll see the difference between them in days. In LibreOffice, I see “25,569.00” – reveal the decimals to avoid a rounding error.
Now, to convert, I use this formula in cell B2:
=(A2/86400+25569)
where A1 is the cell with the unix epoc seconds
I format B2 as whatever date/time I like to see.
Then I copy the formula in B2 all down the B column to convert cells in all the other rows.