Unix time stamp to date and hour in google sheets-CodePudding

how can I convert my Second Time stamp (1634978274) to a form that it shows me 2021-10-23 08:23:50, so that it displays the date AND the time in hours and minutes (seconds are not necessary)?

CodePudding user response：

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)

CodePudding user response：

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