Unix epoch time is the date and time that is measured by the number of seconds elapsed since 00:00:00 UTC on 1 January 1970.
You can convert Unix Epoch Time to Date in Google sheets by dividing the number by 86400 and adding the date 1 January 1970.
Basic Example
=(B2 / 86400) + DATE(1970, 1, 1)The above formula converts the Unix epoch time available in cell B2 into date time.
This tutorial teaches you the different methods to convert Unix Epoch Time to Date using the Google sheets formula or Google Apps script function.
Convert Epoch to Date Using Formula
One day has 86400 hundred seconds. Since the Unix epoch time is represented in seconds, you need to divide the epoch by 86400.
To convert the Unix epoch to Date using a formula,
- Divide the epoch by 86400.
- Add the epoch start date 1-1-1970
Formula
The following formula converts the Unix epoch in cell B2 into a date.
=(B2 / 86400) + DATE(1970, 1, 1)Formula Usage
Convert Epoch to DateTime (With Timezone)
Different time zones have different times. When converting the epoch to DateTime, you may need to consider the appropriate time zone.
To convert epoch into a DateTime with timezone,
- Divide the epoch by 86400
- Add the epoch start date 1-1-1970
- Add the time zone. For example, to add the time zone GMT +5:30, add time(5,30,0). If your timezone is negative, subtract the time zone.
Formula
The following formula converts the Unix epoch time in cell B3 into Datetime based on the timezone GMT +5:30.
=(B3/86400)+DATE(1970,1,1) + time(5,30,0)Output
2/10/2023 10:18:49Using Apps Script Function to Convert Epoch to Date
This section teaches how to use the Apps script function to convert Unix Epoch into a Date.
The advantage of this method is that you can create a function once and invoke the function in as many cells as you want to convert.
You can create an Apps script using the menu Extensions -> Apps Script.
- Create an Apps script in the Google sheet where you want to perform the conversion
- Place the following function with the name
fromUnixEpoch() - This function converts the epoch into milliseconds and creates a new date based on the milliseconds
Code
function fromUnixEpoch(epoch_in_secs) {
return new Date(epoch_in_secs * 1000); // Convert to milliseconds
}Using the function in Google sheets
The following formula converts the epoch available in cell B7 into a Date time using the function defined in the Apps script.
=fromUnixEpoch(B7)Output
2/10/2023Using Floor While Converting Epoch to Date
While converting an epoch into a Date, it is better to floor the date because the Date object will also have time defined in it though it is not explicitly defined in it.
When you compare two dates, for example, “2/10/2023” in one cell might not be equal to “2/10/2023” in another cell because of the different time information available in the Date object.
When you floor() it, the time object will reset to 0:00:0 for the date objects.
Formula
=FLOOR(B3/86400)+date(1970,1,1)+time(0,0,0)Output
2/10/2023 0:00:00