calculate date differences between two columns, for Google Apps Script / GSheets

date differences in google spreadsheets.js

// Subtracts two dates, formatted as e.g. 2025-04-10T12:46:10
// Author: Eric Mill, [email protected]

// columns with notBefore and notAfter dates
var startField = "D";
var endField = "E";
var resultField = "F";

// first sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

// row range
var startRow = 23029;
var endRow = sheet.getLastRow();

function updateLifetimes() {  
  for (var i=startRow; i<(endRow+1); i++) {
    var startDate = new Date(sheet.getRange(startField + i).getValue());
    var endDate = new Date(sheet.getRange(endField + i).getValue());

    var lifetime = parseInt((endDate - startDate) / 1000 / 60 / 60 / 24);
    sheet.getRange(resultField + i).setValue(lifetime);
    
    Logger.log("[" + i + "] Updated to " + lifetime + ".");
  }
}