We help IT Professionals succeed at work.

# Google Sheets, days between dates calculation

on
Medium Priority
93 Views
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(R[0]C[-7],R[0]C[-1],\"D\")+1");
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}
``````

Comment
Watch Question

## View Solution Only

Analyst 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");
``````

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

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.

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

Analyst 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?

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

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

Analyst Assistant
CERTIFIED EXPERT

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