Unix time stamp to date and hour in google sheets

Question

I am working in Google Sheets. How can I convert a timestamp in seconds (as type of 1634978274) to the format of 2021-10-23 08:23, e.g. Date, hours; Minutes and how can I store this result in a new column? The original timestamp as such should not be changed, though.

Unfortunately, I am not sure by which number to divide the timestamp as such to receive the correct date.

I strive to filter for the dates and hours, which is why I need to transform the timestamp in the first place.

Answer 1

if your unix / epoch time is in seconds use:

=TEXT(A2/86400+DATE(1970, 1, 1), "dd/mm/yyyy hh:mm:ss")

without seconds:

=TEXT(A2/86400+DATE(1970, 1, 1), "dd/mm/yyyy hh:mm")

---

if your unix / epoch time is in milliseconds use:

=TEXT(A2/86400000+DATE(1970, 1, 1), "dd/mm/yyyy hh:mm:ss.000")

---

for array use:

=INDEX(IF(A1:A="",,TEXT(A1:A/86400+DATE(1970, 1, 1), "dd/mm/yyyy hh:mm:ss")))

Answer 2

Unix time counts the number of elapsed seconds from an origin of January 1, 1970. So to convert to Sheets time:

=DATE(1970,1,1) + 1634978274/(60*60*24)

... where 60*60*24 = "60 seconds per minute x 60 minutes per hour x 24 hours per day."

Then you can format the formula cell (or range) with the Date/Time format of your choice.

If your Unix time will be entered into a cell, of course you can substitute 1634978274 in the formula with that cell reference, e.g.:

=DATE(1970,1,1) + A1/(24*60*60)