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!

  • 1
    Welcome. Please follow the tour, read How to Ask, add a brief description of your search/research efforts. It's worthy to note that usually also is helpful to include a link to a demo spreadsheet shared with anyone with the link to view. May 3, 2020 at 15:16
  • 2
    Try to use IF and ISBLANK May 3, 2020 at 15:17
  • The calculated cell is not blank but the closing date is ... hmmm If the closing date isblank then calculated cell = null ? How would I wrap that in my current formula?
    – Donavon
    May 3, 2020 at 20:44
  • One of the reasons we ask for "a brief description of your search/research efforts" is that it's not productive to double up on research effort.
    – Tedinoz
    May 4, 2020 at 15:01
  • Hi Tedinoz. I provided the code that I tried and my brief description. How is this unproductive? Sorry to have wasted your time. ;(
    – Donavon
    May 4, 2020 at 20:36

1 Answer 1



=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

  • I tried my best to interpret the code and make it work. My syntax is not right. Here is a link to a Google Sheet test page with the scenario. docs.google.com/spreadsheets/d/… Again, thank you ;)
    – Donavon
    May 4, 2020 at 20:30
  • @Donavon Your function in Column C looks fine to me; I added another in Column D - same result. All the values where the closing date is not known appear as blanks. The idea was to get rid of the "big" negative values, not the negative values, per se, right?
    – Tedinoz
    May 5, 2020 at 0:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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