Convert Unix Epoch Time to Date in Google Sheets
JavascriptDatetimeGoogle Apps-ScriptGoogle SheetsEpochJavascript Problem Overview
I have a sheet with a column of unix epoch times (in seconds): 1500598288
How can I convert these into normal dates?
Javascript Solutions
Solution 1 - Javascript
The simplest way, not requiring any JS programming, would be through a formula, dividing by 86400 seconds per day and adding to January 1, 1970. For example the following gives 21 July 2017:
=1500598288 / 86400 + DATE(1970, 1, 1)
To convert a whole column of numbers, just use ARRAYFORMULA:
=ARRAYFORMULA(A:A / 86400 + DATE(1970, 1, 1))
Solution 2 - Javascript
Make a custom function using the javascript Date function:
Tools > Script Editor... >
function FROM_UNIX_EPOCH(epoch_in_secs) {
return new Date(epoch_in_secs * 1000); // Convert to milliseconds
}
And then in a new column you can do =FROM_UNIX_EPOCH(A1)
Solution 3 - Javascript
now, 3 years later from the original post, you can make...
Tools > Script Editor... >
function millisToDate(timeInMillis){
var yourDateFromMillis = new Date(timeInMillis);
return yourDateFromMillis;
}
and use your new function in the cell...
=millisToDate(cell here)
Solution 4 - Javascript
Add this for Epoch Timestamp to DateTime (GMT adjustment) in google sheets cell
=A1/86400+DATE(1970,1,1)+time(5,30,0)
Solution 5 - Javascript
Building on the accepted answer, you will want to use FLOOR() if you want to be able to compare the dates
=FLOOR(1500598288/86400)+date(1970,1,1)
Otherwise, "7/21/2017" in one cell might not equal "7/21/2017" in another cell.
Solution 6 - Javascript
In AppScript Paste this :
function EpocToDate(epoch) {
//var epoch = 1451606400000; 12/31/2015 19:00:00
return (Utilities.formatDate(new Date (epoch),'America/New_York','MM/dd/yyyy HH:mm:ss'));
}
Use in Google Sheets as:
=EpocToDate(1451606400000)
Incase if you have to refer from another cell :
=EpocToDate(VALUE(C2))
Solution 7 - Javascript
The formula given in the answer by ttarchala is incorrect. Here is a more expansive answer.
I'd recommend not replacing the data in the of seconds since epoch times column. You may need it for sorting, etc. Additionally, it will be difficult to apply the formula to an entire column. Instead, initially insert a new column next to it.
Click in the new cell adjacent the topmost epoch cell. Assuming that the topmost epoch cell is A1, enter this formula into the formula box for the new cell.
=A1/8640000+date(1970,1,1)
The date time should be displayed in your new cell. By default, it will only show the date and not the time. To make it show the time as well you need to change the cell format. With your new date cell selected, click the 123 format drop down menu and select the required cell format.
To apply this formula to all of the cells in the column, click the small black square (handle) at the bottom right of the date / time cell and drag it to the bottom of the column. Once you release the drag the columns should be populated with the date time conversion for its adjacent epoch data cell. You can see the epoch data cell index changed in the formula as you select each cell separately.