Search code examples
dategoogle-sheetssumgoogle-sheets-formulaarray-formulas

Sum values in a range of dates with a timezone in it

I have a long list of dates with this format:

2019-11-28 10:12:48 -0800

The problem is that the formula I use to sum values in a range of dates do not recognize the cell as a Date. I have to manually delte the -0800 but I have a very long list.

There is any way to format the cells as a date or automatically get rid of the -0800 everytime I import a file using a function?

Solution

  • You can use the following script to remove every occurrence of -0800. This will result in valid dates in your Sheets document.

    function myFunction() {
  var sheet = SpreadsheetApp.getActive().getSheets()[0];
  sheet.getDataRange().createTextFinder(" -0800").replaceAllWith("");
}

    The function uses the TextFinder class of Google Apps Script. You may learn more about it and the methods used with it in the following link: