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:

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

-

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 !!

-