Solved

google sheets dynamically calculate days between two dates

Posted on 2016-11-22
10
46 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Learn new improvements released by Google for Google Calendar. Noted in this article are simple tips and tricks that can make your everyday use of Google Calendar better.
Working with spreadsheets can be a daunting task, especially when having to deal with large amounts of data. All you see are rows and rows of numbers and soon your eyes begin to glaze over. Take advantage of the tools in Google Sheets to create prof…
This Micro Tutorial will demonstrate the easy use of Gmail embedding images in your email so the recipient of your email can view them in context.
This Micro Tutorial will demonstrate common damaging and frequent mistakes I see in most analytic audits. Most of them are campaign tagging mistakes, so this video will break it down into simple steps.

948 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now