Insert the result count number of days between two

Question

How to put in a Cell the number of days pass from a specific date. For example:

Column1: 12/05/2013 18:00:00 Actual Days using Function New Date() Column2: The script will insert the numbers of days (Today - Column1) = 4

The script should be insert in the column 2 the number of days pass from today. Today is 16/05/2013 23:00, the script will insert 4. If possible due this?

I'll appreciate if you can help me.

That function was passed by friend but I think doesn't work because the date is not in milliseconds but the result in the cell should be number of days

    }
    function formatting2(event) {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // get the sheet
      var columnF = sheet.getRange(2, 1, sheet.getLastRow()-1, 1);
      var updateage = sheet.getRange(2, 2, sheet.getLastRow()-1, 1);
      var fValues = columnF.getValues(); // get the values
      var result = new Date();

      updateage.setValue(result-fValues)
    }  

Answer 1

This is a possible way to get the result you want, I wrote it as a custom function, ie you have to put it in the the cell where you want it to appear in the form =dayToToday()

here is the script with a few logs and comments to show intermediate values.

function dayToToday(x){
  var sh = SpreadsheetApp.getActiveSheet();
  Logger.log(sh.getActiveCell().getRowIndex())
  var refcell = sh.getRange(sh.getActiveCell().getRowIndex(),1).getValue();;// get value in column A to get the reference date
  var refTime = new Date(refcell);
  var ref = refTime.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
  var today = new Date();
  var TD = today.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
  var day = parseInt(TD-ref);// get the difference in days (integer value )
  return day ; // return result that will be in cell
}

---

EDIT : if I understand your use case I suggest you abandon the custom function aproach (that I never use since I don't like it so much) and go for this solution that uses a timer trigger and/or a menu to update values in your spreadsheet in one batch.

(ps : sorry for not having answer quickly enough... too busy these days :-)

function onOpen() {
  var menuEntries = [ {name: "test function", functionName: "toTrigger"},
                     ];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu("test menu",menuEntries);//
}

// create a timer trigger that will call "toTrigger" every 15 minutes

function toTrigger(){
  var sh = SpreadsheetApp.getActiveSheet();
  var data = sh.getRange(1,1,sh.getLastRow(),2).getValues();
  for(var n=0;n<data.length;++n){
  if(typeof(data[n][0])=='object'){
    data[n][1]=dayToToday(data[n][0])
      }
    }
   sh.getRange(1,1,sh.getLastRow(),2).setValues(data) 
}




function dayToToday(x){
  var refcell = x;;// get value in column A to get the reference date
  var refTime = new Date(refcell);
  var ref = refTime.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
  var today = new Date();
  var TD = today.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
  var day = parseInt(TD-ref);// get the difference in days (integer value )
  return day ; // return result that will be in cell
}

---

second EDIT:

when using var data = sh.getRange(1,1,sh.getLastRow(),2).getValues();

the numbers in getRange are start row, start column, number of Rows, number of Columns so you'll have to gat a range that includes the columns you want and then use data [n][columnNumber-1] to get the corresponding value. (-1 because array start from 0 and columns count from 1 (A in fact but A is first column ;-)) if you need a variable reference then let me know because the function must be modified to accept 2 variables (a ref and another one).

---

LAST EDIT (I hope)

I changed some details... see [9] and [5] index corresponding to J and F

function toTrigger(){
  var sh = SpreadsheetApp.getActiveSheet();
  var data = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues();
  for(var n=0;n<data.length;++n){
  if(typeof(data[n][9])=='object'){
    data[n][5]=dayToToday(data[n][9])
      }
    }
   sh.getRange(1,1,data.length,data[0].length).setValues(data) 
}

---

update/edit

result in hours (following request in comments)

function hoursToToday(x){
  var refcell = x;;// get value in column A to get the reference date
  var refTime = new Date(refcell);
  var refhrs = refTime.getHours();
  var ref = refTime.setHours(refhrs,0,0,0)/3600000;// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
  var today = new Date();
  var todayHrs = today.getHours()
  var TD = today.setHours(todayHrs,0,0,0)/3600000;// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
  var hrs = TD-ref;// get the difference in days (integer value )
  return hrs ; // return result that will be in cell
}

---