Solved

google sheets dynamically calculate days between two dates

Posted on 2016-11-22
10
96 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Whether you’re looking to gather data for research or gather feedback on an idea, being able to build and distribute your own online survey is not only cost-effective, but allows you to reach a larger audience and receive results in real-time. Googl…
This article discusses how to create an extensible mechanism for linked drop downs.
This Micro Tutorial demonstrates how to create custom reports and the secrets of determine the metrics and dimensions for your data that works best with your needs.
By using UNIQUE function in Google Sheets, you can get around removing duplicates like in Microsoft Excel.

636 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