11

I am using the following formula in a Google Sheets to calculate the number of days between two dates (dates are in columns G and AL):

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

It has to be an array because I am working with a Google Forms and entries are automatically populated into new rows.

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?

Improve this question

4 Answers 4

Reset to default
12

Google Sheets as well as other spreadsheet application handles dates as serialized number. The large number that you refer that is displayed when a cell on AL column is blank is very likely that it is the number that represents the date on the corresponding cell on column G.

Regarding returning a blank, try something like this:

=ArrayFormula(IF(ISBLANK(A:A),,"Non blank"))

Please note that the second argument of the IF() function is left empty (,,): IFS(), IF() and other similar functions return blank when the corresponding argument is empty.

Improve this answer
0
3

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!

Improve this answer
2
  • 5
    ""isn't blank (no value at all) Commented May 2, 2018 at 19:03
  • The =true is unnecessary. You can just do AND(ISBLANK(A1),ISBLANK(B1)) and NOT(ISBLANK(A1)) for false Commented Jul 9, 2019 at 18:56
2

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

Improve this answer
0 
=IF(ISBLANK(A1),"",TODAY()-A1)
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.