Search code examples
javascriptgoogle-apps-scriptgoogle-sheetscustom-function

How to use this JS function in Google Sheets? Using cell values in var and loop

So, I have this one function that calculates the number of Thursdays between two dates:

var startDate = new Date('2021/8/16');
var endDate = new Date('2022/3/15');

function THURSDAYS( days, d0, d1 ) {
  var ndays = 1 + Math.round((d1-d0)/(24*3600*1000));
  var sum = function(a,b) {
    return a + Math.floor( (ndays+(d0.getDay()+6-b) % 7 ) / 7 ); };
  return days.reduce(sum,0);
}

return(THURSDAYS([3],new Date(startDate),new Date(endDate)));

enter image description here

And I'm trying to use it as a custom function in Google Spreadsheet like here where it would return the sum/total of Thursdays found on each line. So I need to use cell values in startDate and enDate, then create a loop... Still learning so I guess I get easily fried up LOL

Anyways, thank you for any help you can give!

Solution

  • You don't need a custom function. Try

    =SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT( A2 &":"& B2));2)=4))