Solved

google sheets dynamically calculate days between two dates

Posted on 2016-11-22
10
69 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Generate Numbers in JQuery file 11 66
multiple selects 23 49
100% tall div not scrollable on iPhone 3 21
How to make a good PHP + MySQL + JS pagination system? 3 35
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate Google Calendar to monitor updates with top sites, such as Facebook, Google, Twitter, etc. with Marketing News. Each update of Google Calendar can be monitored, correlate dips and spikes in your website traffic, …
Shows how to create a shortcut to site-search Experts Exchange using Google in the Chrome browser. This eliminates the need to type out site:experts-exchange.com whenever you want to search the site. Launch the Search Engine Menu: In chrome, via you…

821 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