Convert Unix Epoch Time to Date in Google Sheets

Question

I have a sheet with a column of unix epoch times (in seconds): 1500598288

How can I convert these into normal dates?

Answer 1

As of 2023, GSheet introduced a new function, EPOCHTODATE:

=EPOCHTODATE(1500598288)

To convert a whole column of numbers, just use ARRAYFORMULA (or BYROW, if you are LAMBDA-inclined):

=ARRAYFORMULA(EPOCHTODATE(A:A)

 

--- The answer below still works, but is outdated as of 2023 ---

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))

Answer 2

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)

Answer 3

Add this for Epoch Timestamp to DateTime (GMT adjustment) in google sheets cell

=A1/86400+DATE(1970,1,1)+time(5,30,0)

Answer 4

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)

Answer 5

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.

Answer 6

Building on the other awesome answers, here's what I use to get the full date time from an epoch timestamp. Tested with Google Sheets.

=QUOTIENT(A1, 86400) + date(1970, 1, 1) + time(quotient(MOD(A1, 86400), 3600), quotient(mod(A1, 3600), 60), mod(A1, 60))

Some examples:

epoch	date time
0	1970-01-01 0:00:00
61	1970-01-01 0:01:01
3599	1970-01-01 0:59:59
86403	1970-01-02 0:00:03
2678402 (=86400*31+2)	1970-02-01 0:00:02
31536008 (=86400*365+8)	1971-01-01 0:00:08

Answer 7

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))

Answer 8

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.