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.

score 6 · Accepted Answer · answered Nov 05 '21 at 03:36

6

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")))

answered Nov 05 '21 at 03:36

player0

124,011
12
67
124

1

Thank you a lot; this works! – Baobab Dec 15 '21 at 14:23
I have now a timestamp of the form 1643733039331616475 and it doesn't work anymore.. could you help me out @player0? – Baobab Mar 09 '22 at 13:23
@Baobab is this it? https://i.stack.imgur.com/kLd2N.png – player0 Mar 09 '22 at 17:33

score 3 · Answer 2 · answered Nov 04 '21 at 18:33

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)

Thank you, this alternative works as well. – Baobab Dec 15 '21 at 14:24 — Baobab, Dec 15 '21 at 14:24

Unix time stamp to date and hour in google sheets

2 Answers2