Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I am using the following formula in a Google spreadsheet to calculate the number of days between two dates (dates are in columns G and AL). The problem is that some of the cells in col AL are blank, so the formula is reading those dates as something else and returning a very large number. Also, when both cells are blank, the formula returns a 0, messing up my data. How can I include something to ignore blank cells or just return a blank when one or both cell references is blank?

Here is my formula:

=ARRAYFORMULA(if(row(A:A)=1, "Readmission Days", (G:G)-(AL:AL)))

PS: The formula has to be an array because I am working with a Google form and entries are automatically populated into new rows.

share|improve this question
add comment

2 Answers

Tried to find all kinds of solutions with formulas, but wasn't successful. Therefore I wrote this very small script to do the work for you:

function myCalc(range1,range2) {
  if(range1.length != range2.length) {
    throw "Both ranges need to be the same !!";
    return;
  }

  var l = range1.length;
  var output = [];

  for(i=0; i<l; i++) {
    if(range1[i] == 0 || range1[0] == "" ||
      range2[i] == 0 || range2[0] == "") {
      output.push("");
    } else {
      output.push([Number(range1[i]) - Number(range2[i])]);
    }
  }  
  return output;
}

Under Tools>Script editor you can paste the code. Press the bug button (will safe and de-bug) and you're on the go !!

share|improve this answer
add comment

You can still use nested IFs:

=ARRAYFORMULA(if(row(A:A)=1,"Readmission Days",
  if(G:G="","",if(AL:AL="","",(G:G)-(AL:AL)))))

There's no need to make a script to do that :)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.