Zac123
asked on
google sheets dynamically calculate days between two dates
hi, this code moves row data from one tab to another and at the same time logs the date and puts the date in the last column. i then end up with Col A with a date and Col H with a date. I'd like to automatically calculate the days inbetween
example
A1 = 22/10/2016 H1 = 24/10/2016 I = 3 days
Here is my code. you'll see the commented out bit is the bit i have been playing with.
example
A1 = 22/10/2016 H1 = 24/10/2016 I = 3 days
Here is my code. you'll see the commented out bit is the bit i have been playing with.
function runOnEdit2(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == "dataEntry" && r.getColumn() == 8 && r.getValue() == "Yes") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Completed");
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 H
targetSheet.getRange(targetSheet.getLastRow(),numColumns + 0).setValue(new Date());
//targetSheet.getRange(targetSheet.getLastRow(),numColumns + 1).setValue(ArrayFormula( if(len(A1:A), B1:B-A1:A, ) )());
s.deleteRow(row);
could you confirm your column are date and NOT string
try this :
targetSheet.getRange(targetSheet.getLastRow(),numColumns + 1).setFormula("=DATEDIF(R[0]C[-7],R[0]C[-1], \"D\")");
ASKER
D is for the number of day
so look like wev'e 41462 days between 17/06/1909 and 27/11/2016 ? at first look...
check this page : https://support.google.com/docs/answer/6055612?hl=en
so look like wev'e 41462 days between 17/06/1909 and 27/11/2016 ? at first look...
check this page : https://support.google.com/docs/answer/6055612?hl=en
ASKER
17/06/1909... but why is this date relevant? where did this date come from?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
-8 did it for me. thank you very much for your help
Open in new window