troubleshooting Question

Google Sheets, days between dates calculation

Avatar of Zac
ZacFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft OfficeGoogleGoogle WorkspaceMicrosoft ExcelScripting Languages
8 Comments1 Solution153 ViewsLast Modified:
Hello Experts,

in plain English...

if "yes" is selected, move row to a different sheet.
also add the current date into the Col. and
now calculate the total days between col A & G and display results in H

for some reason it doing the calctulation for the row above, not the current row that has just been populated.

any ideas please?

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Active Jobs" && r.getColumn() == 7 && r.getValue() == "yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed Jobs");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    //This puts the completion date into the last column of data currently col G
    targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 0).setValue(new Date());
    //Calculates the turn around time in days for column I on the Completed sheet
    targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 1).setFormula("=DATEDIF(R[0]C[-7],R[0]C[-1],\"D\")+1");
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}


https://docs.google.com/spreadsheets/d/1X4M2G0f7MEpDETrVIZ2kU9MJAFC8K9jR9ZqKOijYDN0/edit?usp=sharing
ASKER CERTIFIED SOLUTION
NorieSenior Associate
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros