1

I have 4 cells on the same row that are either blank or contain a date (YYYY-MM-DD), which I need to compare to today's date. If any one cell is blank or occurs in the past, I want the result to reflect a "No" Answer.

To get a "Yes" result, all four cells must occur today or in the future.

I tried a nested IF-THEN statement, but am getting a "Yes" if at least one cell has today's date or later.

Here's my attempt:

=IF(J4>=TODAY(),"Yes",IF(K4>=TODAY(),"Yes",IF(L4>=TODAY(),"Yes",IF(M4>=TODAY(),"Yes","No"))))

resulting in a "Yes" in cell N4

However, in my example, J4 and L4 both have dates that occur in the future, whereas K4 and M4 are blank, so the answer I want here is a "No". In addition, if all of cells are blank or all have dates that have already passed, I want a "No" result as well. Also, if some dates are current, but others are in the past, I also want a "No" result.

Any help you can give would be greatly appreciated.

Calculation Example

Improve this question

2 Answers 2

Reset to default
1

You can use COUNTIF:

=IF(COUNTIF(J4:M4,">="&TODAY())=4,"Yes","No")

enter image description here

Or you can use AND() IF the cells are truly blank and NOT a formula that returns "":

=IF(AND(J4:M4>=TODAY()),"Yes","No")

enter image description here

Improve this answer
3
  • Thanks so much, Scott! I thought about using COUNT, but wasn't sure of the syntax. I didn't think about COUNTIF.
    – William Woodson
    Commented Feb 9, 2022 at 21:36
  • I got your answer to work, Scott, but I just edited my original question to ask another. If you, or anyone else, has time, maybe you could take another look. Basically, I have another column that I would like to automatically default to today's date, if anything on that specific line gets edited/added or changed in some other way.
    – William Woodson
    Commented Feb 10, 2022 at 17:34
  • That is not appropriate for this forum. Once you receive answers, changing the question invalidates the answer. Instead ask a new question. Please revert this question back to the original. It opens me up for down votes.
    – Scott Craner
    Commented Feb 10, 2022 at 17:54
0

the most succinct method I can think of is to use AVERAGE with comparison in IF

blank cells will be set to the serial number of 0 if formatted as dates and will display as 01/01/1900

=IF(AVERAGE(range)<TODAY(),"No","Yes")

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .