CODEWITHSUNDEEP
datetimegoogle-sheetsgoogle-apps-scriptepoch
Mike Bjorge
Mike Bjorge

Reputation: 2121

Convert Unix Epoch Time to Date in Google Sheets

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

How can I convert these into normal dates?

Upvotes: 123

Views: 184131

Answers (8)

ttarchala
ttarchala

Reputation: 4567

Modern (since 2023)

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

enter image description here

Legacy (without EPOCHDATE)

Before the EPOCHDATE function was introduced, the simplest way, not requiring any JS programming, was 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))

Upvotes: 217

sukumar yadagiri
sukumar yadagiri

Reputation: 11

In Apps Script 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))

Upvotes: 1

Liu Zehua
Liu Zehua

Reputation: 431

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

Upvotes: 5

adaao mascarelli
adaao mascarelli

Reputation: 121

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)

Upvotes: 12

UserBSS1
UserBSS1

Reputation: 2211

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

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

Upvotes: 13

emmby
emmby

Reputation: 100464

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.

Upvotes: 8

markhorrocks
markhorrocks

Reputation: 1418

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.

Upvotes: -8

Mike Bjorge
Mike Bjorge

Reputation: 2121

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)

Upvotes: 46

Related Questions