Google-sheets – Hide calculated column till all data is present

formulasgoogle sheets

About what I am trying to do:
I track several homes for sale. My problem child is column W 'Days to closing'.

This field is automatically calculated.
Column S contains the closing date
Data!J3 is the cell that contains Today() (I reference this field a lot)

I am trying to display how many days till closing. This works fine, however, I am seeing large negative numbers because I do not know the closing date yet.

QUESTION:
How can I hide the large negative numbers till I know the date closing?

My current formula today
=ARRAYFORMULA(DAYS(S4:S,DATA!J3)) —> Data!J3 is today()
All cells in column S must automatically calculate thus ARRAYFORMULA

Any formula ideas welcome!

Best Answer

Try:

=ArrayFormula(IF(B4:B <> "", DAYS(B4:B,$D$2), ""))

Closing date = Column B Today's date = Cell D2

Based on the answer by Robin Gertenbach in ArrayFormula IF ROW Not empty then iterate through formula

Related Topic