We help IT Professionals succeed at work.

Google Sheets, days between dates calculation

Medium Priority
93 Views
Last Modified: 2020-03-05
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);
  }
}

Open in new window



https://docs.google.com/spreadsheets/d/1X4M2G0f7MEpDETrVIZ2kU9MJAFC8K9jR9ZqKOijYDN0/edit?usp=sharing
Comment
Watch Question

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Zac

Why do you have R[0] in the formula?

If you want the formula to reference the row it's in you don't need the [0].
targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 1).setFormula("=DATEDIF(RC[-7],RC[-1],\"D\")+1");

Open in new window


P.S. It's not actually possible to edit the documents you are posting links to.
Zac

Author

Commented:
hi thanks for getting back. ive now made the sheet editable.
working remotely via my phone at the mo but i just copied and pasted and it looks like its doing the same. ill have a proper look when i get back. thanks agaain.

Zac

Author

Commented:
Good morning, ive tested this the best i can and get the same results. Sorry my knowledge is very lacking in this area. Really not sure what to try next.
thanks
zac

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Zac

Do you mean it's still not putting the correct formula in the cell or is it not working in some other way?
Zac

Author

Commented:
Sorry, i'm my opinion from what i understand... it is not putting the date into the last cell (G) and therefore is not calulating the in cell (H)
Analyst Assistant
CERTIFIED EXPERT
Commented:
It could be that you are overwriting the date, and perhaps the formula, when you are copying/moving the data from Active Jobs to Completed Jobs.

Try switching the order in which things are happening.

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

   ' first move job details to Completed Jobs
    s.getRange(row, 1, 1, numColumns).moveTo(target);

    //This puts the completion date into the last column of data currently col G
    targetSheet.getRange(targetSheet.getLastRow(),numColumns).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(RC[-7],RC[-1],\"D\")+1");
    
    s.deleteRow(row);
  }
}

Open in new window

Zac

Author

Commented:
ahh yes this makes sense. thank you. I have corrected a couple of typos in yours above and its working perfectly. thanks again:
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);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    //This puts the completion date into the last column of data currently col G
    targetSheet.getRange(targetSheet.getLastRow(),numColumns).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(RC[-7],RC[-1],\"D\")+1");
    s.deleteRow(row);
  }
}

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Oops, used a ' instead of // for the comment.:)