Google Sheets – Arrayformula with Dates Before and After

google sheetsgoogle-apps-script

Here is what my spreadsheet looks like

My Chart

I enter recurring weekly payments details in G,H,I,J & K (Name,Amount,Day of week, Start Date, End Date respectively). This arrayformula: 

=arrayformula(sumif(I2:I32,C2:C32,H2:H32))

shows the amount due (D), on the Date (A), whose day of the week (C) matches the one defined in I.

Link to example to play with

If I have something that will be due every Friday for example I put the name for the bill in G, the amount in H, and the day of the week # in I. When I do that column D shows amount that is due and repeats it on all the dates where column C (DOW#) match the provided value in column I.

What I would like to do is
make column D not add the amount if the date in A is before the date in column J or after the date in column K. It should work if neither J or K have values, if J has a value, if K has a value or if both J & K have a value.

All my attempts at figuring out how to compare the dates with gt (>) & lt (<) in the same arrayformula have failed.

Best Answer

I wrote a custom function in Google Apps Script that takes all the criteria you want it to have (and more) in consideration.

Code

function myPayments(paymentPlan, dateRange) {
  var output = [];  
  for(var i=0, iLen=dateRange.length; i<iLen; i++) {
    var checkDate = new Date(dateRange[i]);
    var weekDay = checkDate.getDay()+1, sum=0;
    for(var j=0, jLen=paymentPlan.length; j<jLen; j++) {
      if(paymentPlan[j][1] == weekDay) {   
        if(paymentPlan[j][2] === "" && paymentPlan[j][3] === "") {
          sum += paymentPlan[j][0];
        } else if(paymentPlan[j][2] === "") {
          if(checkDate <= new Date(paymentPlan[j][3])) {
            sum += paymentPlan[j][0];
          }
        } else if(paymentPlan[j][3] === "") {
          if(checkDate >= new Date(paymentPlan[j][2])) {
            sum += paymentPlan[j][0];
          }
        } else if(paymentPlan[j][2] !== "" && paymentPlan[j][3] !== "") {
          if(checkDate >= new Date(paymentPlan[j][2]) && 
            checkDate <= new Date(paymentPlan[j][3])) {
            sum += paymentPlan[j][0];
          }
        } 
      }
    }
    if(sum == 0) {
      output.push([]);
    } else {
      output.push([sum]);
    }
  } 
  return output;
}

Screenshot

enter image description here

Remarks

If you extend the paymentPlan range, to contain the rest of the data as presented in the PAYMENT PLAN sheet, then you will notice that overlapping payments will be summed.

Add the script under Tools>Script editor and press the save button (in your case it's already there).

Example

I've added the solution in your example file.

Related Topic