How to convert Date and time into epoch in google sheet

Question

I have a google sheet where i have a column date and time in the following format

14-08-2021 12:30:00

I want a result in Epoch which is in this format 1591259160

i tried this code in script editor but i am not getting result

    function convertDates(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var range_input = sheet.getRange(1, 1);

  range_input = range_input.getValue();
  range_input = range_input.replace("at ", " ");
  sheet.getRange(1, 1).setValue(range_input);
}

How to get desired result in google sheet

score 0 · Answer 1 · answered Aug 13 '21 at 20:52

0

Try:

new Date(range_input.getValue()).valueOf();

answered Aug 13 '21 at 20:52

Cooper

59,616
6
23
54

score 0 · Answer 2 · edited Dec 29 '22 at 17:12

Personally I have used following formula

=(DATEVALUE(date_cell)-(70*365)-19)*86400+time_in_seconds

A bit of explaination. Google for some reason presents datevalue as index starting (basically 'days since') 1900-01-01, thus in order to get unix epoch time which is seconds since 1970-01-01 you have to add 70 years (70*365), multiply by seconds in day (*86400) and add time converted to seconds (+time_in_seconds). Only the -19 part baffles me, I haven't put much thoughut into it but those may have something to do with leap years. Once you have your epochtime I encourage to doublecheck it.

How to convert Date and time into epoch in google sheet

2 Answers2