0

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!

Improve this question
  • 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. – Rubén♦ May 3 '20 at 15:16
  • 2
    Try to use IF and ISBLANK – Rubén♦ May 3 '20 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 '20 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 '20 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 '20 at 20:36
0

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

Improve this answer
  • 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 '20 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 '20 at 0:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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