dategoogle-sheetstimestampunix-timestampseconds

Unix time stamp to date and hour in google sheets

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.

Solution

  • if your unix / epoch time is in seconds use:

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

    enter image description here

    without seconds:

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

    enter image description here

    if your unix / epoch time is in milliseconds use:

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

    enter image description here

    for array use:

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