Minimum Date Greater than Today()

[VickiMc]

I wanted to write a formula that will find the first date (min) in a range
that is greater/higher than >=today()?
The Match function only likes to return a result less than <today().
My range is non-consecutive cells (Every Fifth column of 240 Columns) in a
row, with multiple cells that would return Greater than today().
And I want the formula to stop evaluating at the first TRUE result.

 

[T. Valko]

My range is non-consecutive cells

(Every Fifth column of 240 Columns)

How about telling us what the range is?

 

[Shane Devenshire]

Hi,

If you mean by first the closest date to today then

=MIN(IF(A1:A10>=TODAY(),A1:A10,""))

Entered as an array - press Shift+Ctrl+Enter

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Hi,

If you mean first date starting from the top of a range, then

=INDEX(A1:A10,MATCH(1,(A1:A10>=TODAY())*ISNUMBER(A1:A10),0))

Intered as an array. The ISNUMBER excludes the possibility of text entries
in the range.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[JulesMacD]

Hi Shane:

How would you modify this formula if you wanted it to evaluate a range of
cells (F6:F10) but if all the dates were prior to today, it would return the
text in cell F11 (which is 'fully vested')?

 

[T. Valko]

Try this array formula**.

Assuming no text in the range.

=IF(COUNTIF(F6:F10,"<"&TODAY())=ROWS(F6:F10),"Fully
Vested",MIN(IF(F6:F10>=TODAY(),F6:F10)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[JoeU2004]

=IF(COUNTIF(F6:F10,"<"&TODAY())=ROWS(F6:F10),
"Fully Vested",MIN(IF(F6:F10>=TODAY(),F6:F10)))

Or simply:

=IF(COUNTIF(F6:F10,">="&TODAY()),
MIN(IF(F6:F10>=TODAY(),F6:F10)),
"Fully Vested")

Again, that is an array formula. Commit with ctrl+shift+Enter instead of
simply Enter. If you mistakenly commit with Enter, select the cell, press
F2, then press ctrl+shift+Enter.

PS: Instead of using TODAY() in the formula, I suggest that you put
=TODAY() in some cell, then reference the cell. If that cell is A1, the
formula becomes:

=IF(COUNTIF(F6:F10,">="&$A$1),
MIN(IF(F6:F10>=$A$1,F6:F10)),
"Fully Vested")

The reason is: I suspect you will discover that you do not want the
function TODAY() at all, but the value of TODAY() at some time ( i.e.
ctrl+; ). So, for example, when you email the file to someone, the results
will not change when they open the file. It will be easier to change your
design if "today's date" is in one cell instead of repeated throughout the
worksheet.