Epoch Converter

We have this number 1638979200 using block.timestamp and we want to know the time and date using a formula.

The datetime for the number 1638979200 is 12/8/21 13:00:00

What would be the formula?


NM, solved it. The formula is:
=(((A1/60)/60)/24)+DATE(1970,1,1)-TIME(3,0,0)

There was a 3 hours offset because of the timezones.

See: https://www.epochconverter.com/

Microsoft Excel / LibreOffice Calc =(A1 / 86400) + 25569 Format the result cell for date/time, the result will be in GMT time (A1 is the cell with the epoch number). For other time zones: =((A1 +/- time zone adjustment) / 86400) + 25569.

Do you know what this number represents? Excel's date/time serial number system is based on "days since midnight on the morning of 0 Jan 1900". The other system I see is based on "time (usually seconds) since midnight on the morning of 1 Jan 1970." If I assume this latter for this number, =CONVERT(1638979200,"sec","day")+DATE(1970,1,1) results in 44538 2/3 or 4 PM on dec 8 2021. At this point, I am guessing that your number represents seconds since 1 Jan 1970 for a specific time zone (maybe UTC??). A formula like I used (adding an adjustment for time zone) should work.

Am I guessing correctly?

Again, see the link.

What is epoch time?

The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Literally speaking the epoch is Unix time 0 (midnight 1/1/1970), but 'epoch' is often used as a synonym for Unix time. Some systems store epoch dates as a signed 32-bit integer, which might cause problems on January 19, 2038 (known as the Year 2038 problem or Y2038). The converter on this page converts timestamps in seconds (10-digit), milliseconds (13-digit) and microseconds (16-digit) to readable dates.