If you need to convert a timestamp in Unix format into a human readable date, you can apply two type of formula’s without any knowledge of javascript or Google Scripts.
To convert a cell with a timestamp into a date, apply the following command:
= CELL_POSITIION / 86400 + DATE(1970, 1, 1)To apply the convert formula to an entire column, use the following formula on top of the column in which you want the result:
=ARRAYFORMULA(COLUMN_WITH_VALUES:COLUMN_WITH_VALUES / 86400 + DATE(1970, 1, 1))
Examples - single convert
= 1625744638 / 86400 + DATE(1970, 1, 1) gives 7/8/2021
In the example below:
- column I contains the Unix-timestamp;
- column J holds the Unix timestamp devided by 1000, needed for the formula.
- column K holds the formula =J2 / 86400 + DATE(1970, 1, 1).
Deviding the Unix-timestamp by 1000, is something you can also do in the formula, but it depends what the original timestamp-format is.
Examples – full column convert
=ARRAYFORMULA(J:J / 86400 + DATE(1970, 1, 1))
When pasting the formula in K1, all the values from column J will be converted with the result displayed in column K.
