Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

google sheets dynamically calculate days between two dates

Posted on 2016-11-22
10
Medium Priority
?
107 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
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.

 
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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
By using UNIQUE function in Google Sheets, you can get around removing duplicates like in Microsoft Excel.
This Micro Tutorial will demonstrate how marketers can use the Mobile Emulation Tool in Chrome Developer Tool. This will let you preview your site on any mobile device.

730 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