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.

-

This formula will work to keep blank cells from returning "0" to another cell.

``````=IF( AND( ISBLANK(A1)=true , isblank(B1)=true ) , "dontsayzero")
``````

Then, you can just remove the dontsayzero after you've tested it, but LEAVE THE QUOTES! Also, you can make things more sophisticated adding more "IF" and "OR" statements. Check this one out (remove carriage returns but not commas)

``````=
IF( AND(ISBLANK(A1)=true,ISBLANK(B1)=true) , "bothblank",
IF( OR(ISBLANK(A1)=true,ISBLANK(B1)=true) , "atleastoneblank" ,
IF( AND(ISBLANK(A1)=false,ISBLANK(B1)=false) , "neitherblank")))
``````

This formula looks at the two cells and tells you their status. Should be pretty straightforward. This could be used if you had some data, but not enough to make the final calculation and didn't want the zeroes or errors to mess with your further analysis. Just remove the stuff inside, leave the quote signs, and you've got a blank cell for any of the three situations. Moving further, if you want a different operation done in those three situations, remove the quote signs and insert your operation after the comma. There's probably something more sophisticated out there as well. Good luck!

-

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

-

You can still use nested `IF`s:

``````=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 :)

-