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.
any ideas please?
https://docs.google.com/spreadsheets/d/1X4M2G0f7MEpDETrVIZ2kU9MJAFC8K9jR9ZqKOijYDN0/edit?usp=sharing
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
Microsoft OfficeGoogleGoogle WorkspaceMicrosoft ExcelScripting Languages
Last Comment
Norie
ASKER
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.
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.
ASKER
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
thanks
zac
Zac
Do you mean it's still not putting the correct formula in the cell or is it not working in some other way?
Do you mean it's still not putting the correct formula in the cell or is it not working in some other way?
ASKER
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)
ASKER CERTIFIED SOLUTION
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.
ASKER
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);
}
}
Oops, used a ' instead of // for the comment.:)
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].
Open in new window
P.S. It's not actually possible to edit the documents you are posting links to.