"T. Valko" wrote:
=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.








"T. Valko" wrote in message
...
Try this array formula**.

Assuming no text in the range.

=IF(COUNTIF(F6:F10,"<"&TODAY())=ROWS(F6:F10),"Full y
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.

--
Biff
Microsoft Excel MVP


"JulesMacD" wrote in message
...
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')?

"Shane Devenshire" wrote:

Hi,

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

=INDEX(A1:A10,MATCH(1,(A1:A10=TODAY())*ISNUMBER(A 1: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

"VickiMc" wrote:

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.