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.

To find the first date in a range that is greater than or equal to today's date, you can use the following formula:

1. =MIN(IF(A1:A240=TODAY(),A1:A240))

This is an array formula, so you will need to press Ctrl+Shift+Enter instead of just Enter to enter it into the cell.

Here's how the formula works:

1. The IF function checks each cell in the range A1:A240 to see if it is greater than or equal to today's date. If it is, it returns the value of the cell. If it isn't, it returns FALSE.
2. The MIN function then finds the minimum value in the array of values returned by the IF function. This will be the first date in the range that is greater than or equal to today's date.
3. The formula will stop evaluating at the first TRUE result because the MIN function only looks at the values in the array that are greater than or equal to today's date.

Note that if there are no dates in the range that are greater than or equal to today's date, the formula will return a #NUM! error. To avoid this, you can wrap the formula in an IFERROR function:

My range is non-consecutive cells
(Every Fifth column of 240 Columns)

How about telling us what the range is?

--
Biff
Microsoft Excel MVP


"VickiMc" wrote in message
...
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.

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


"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.

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.

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.

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.

"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.