How convert unix timestamp to date in Sheets?

In today’s article, I will show you how to convert Unix time to a readable date time in Google Sheets. Unix time, also known as a Unix timestamp, is a numerical representation of a specific moment in time by the difference in seconds between now and 1.1.1970.

Convert from unix to datetime

Converting from Unix to date is easy. You first have to find out how many days have passed since 1.1.1970. To do this you work out 86400 which is the number of seconds in a day. And then add those days to the date 1.1.1970.

=B3/86400+DATE(1970,1,1)

If you are going to use this function, don’t forget to change B3 to the correct cell with data.

Convert from datetime to unix

Convert datetime to Unix in Sheets could be done by subtracted from today’s date the starting date of unix which is 1.1.1970. After that you will get the number of “days” between this two days and after that you multiply that by the number of seconds which days has 86400 seconds. This will give you the correct number in UNIX.

=(B4 - DATE(1970, 1, 1))*86400

If you are going to use this function, don’t forget to change B4 to the correct cell with data.

What mean if mine unix number has 13 numbers?

Then you must your unix number divide by /1000 before you make conversion.

=B3/1000/86400+DATE(1970,1,1)

If you don’t, the date will be weird:

What is unix timestamp?

A Unix timestamp is number of seconds that have elapsed since the 00:00:00 UTC on January 1, 1970. This number is usually use programmers to transfer time to universal number. I also like to use it in my code, but it’s not really suitable for Google Sheets. So hopefully the formula above will work well for you.