Solved

google sheets dynamically calculate days between two dates

Posted on 2016-11-22
10
65 Views
Last Modified: 2016-11-22
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.

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

Open in new window

0
Comment
Question by:bede123
  • 5
  • 5
10 Comments
 
LVL 82

Expert Comment

by:leakim971
ID: 41898049
Try this :
targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 1).setFormula("=DATEDIF(R[0]C[-1], R[0]C[-7], \"D\")");

Open in new window

0
 
LVL 1

Author Comment

by:bede123
ID: 41898093
nearly. its saying:

screenshot
0
 
LVL 82

Expert Comment

by:leakim971
ID: 41898095
could you confirm your column are date and NOT string
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 1

Author Comment

by:bede123
ID: 41898099
you mean like this?date
0
 
LVL 82

Expert Comment

by:leakim971
ID: 41898100
try this :
targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 1).setFormula("=DATEDIF(R[0]C[-7],R[0]C[-1], \"D\")");

Open in new window

0
 
LVL 1

Author Comment

by:bede123
ID: 41898116
getting closer. now i get some number which dont seeem to mean anything.  i have now set that column to be a number column with only one decimal.screen shot 3
0
 
LVL 82

Expert Comment

by:leakim971
ID: 41898131
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
0
 
LVL 1

Author Comment

by:bede123
ID: 41898133
17/06/1909... but why is this date relevant? where did this date come from?
0
 
LVL 82

Accepted Solution

by:
leakim971 earned 500 total points
ID: 41898145
R[0]C[-7],R[0]C[-1]

try -6 or -8 or -5 instead -7
it's relative to the I column to found the A column
0
 
LVL 1

Author Closing Comment

by:bede123
ID: 41898185
-8 did it for me. thank you very much for your help
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Learn about cloud computing and its benefits for small business owners.
This article discusses how to create an extensible mechanism for linked drop downs.
This Micro Tutorial will demonstrate without sampling how to find out top organic landing pages. The hack gets around the standard way to find the pages in Google Analytics results in sampling for larger sites.
This Micro Tutorial will demonstrate importing calendar invites from events such as webinars into your Google Calendar.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question