  asked on

# Google Sheets, days between dates calculation

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.

``````function onEdit(event) {
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(RC[-7],RC[-1],\"D\")+1");
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}
`````` Last Comment
Norie

8/22/2022 - Mon
Norie

Zac

Why do you have R in the formula?

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

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

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

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

Norie

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

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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Zac

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

Norie

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