Next date after Today()

What I'm trying to accomplish:

In A1:
If a date in H2:H10 is greater than TODAY(), return the value of that date.

If TODAY() happens to be 1/20 and H2:H10 looks like this:

1/5/09
1/6/09
1/13/09
1/14/09
1/19/09
1/15/09
1/22/09
1/27/09
1/8/09
1/28/09


than A1 should have the value of 1/22/09
When TODAY() = 1/28/09, A1 should have no value, preferably no error either, but I can live with a NUM! or Value! or #N/A

Dates will not always be ascending. All cells in H2:H10 may not be filled. There may possibly be a duplicate, but the fact that there is a duplicate is not important to my result. (I'm looking for the 'next scheduled working day' whatever it is)

entered as an array with CTRL + SHIFT + ENTER

A1: =IF(COUNTIF(H2:H10,">"&TODAY()),MIN(IF(H2:H10>TODAY(),H2:10)),"")

once array has been setup the above will appear encased within { }

Originally Posted by DonkeyOte

entered as an array with CTRL + SHIFT + ENTER

A1: =IF(COUNTIF(H2:H10,">"&TODAY()),MIN(IF(H2:H10>TODAY(),H2:H10)),"")

once array has been setup the above will appear encased within { }

When do you use the Ctrl/shift/enter? Instead of Enter? Before ctrl-v?

EDIT: Nvm. got it. If you use Ctrl / Shift/ Enter as often as you can, eventually, one of them will work.
/e mumbles something about a million monkeys on a million keyboards will eventually...

Not sure which you adopted but this:

=IF(COUNTIF(H2:H10,">"&TODAY()),MIN(IF(H2:H10>TODAY(),H2:H10)),"")

Can be broken down into 3 "BITS"

First:

=IF(COUNTIF(H2:H10,">"&TODAY()),do if true, do if false)

So the above will count how many dates in the range specified exceed Today's date - the result will be an integer value between 0 and 9... it's important to note that only the value 0 equates to FALSE in XL and so it follows that should the result of the COUNTIF be anything other than 0 (1 to 9) then this equates to TRUE

So let's assume answer is > 0*, do TRUE action, SECOND BIT, namely:
*based on sample data shown further down

MIN(IF(H2:H10>TODAY(),H2:H10))

So the above is basically going through the range H2:H10 and creating an array of values, where the value of H2:H10 > TODAY that date will be added to the array, if it is <= TODAY() a Boolean of FALSE will be added to the array, so if we assume H2:H10 values of:

H2: 1/5/09
H3: 1/6/09
H4: 1/13/09
H5: 1/14/09
H6: 1/19/09
H7: 1/15/09
H8: 1/22/09
H9: 1/27/09
H10: 1/8/09

And a current date of 1/21 we will end up with an array of values as so:

{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,"1/22/09","1/27/09",FALSE)

Note: that in the above the dates are shown in this way for illustrative purposes, in reality dates are integer values ... on 1900 date system 1/22/09 = 39835 and 1/27/09 = 39840 and these integer values are to all intents and purposes the values held in the array.

We then run the MIN against the above array to return the lowest value from the range as this equates to the earliest date - the logicals (FALSE) will be ignored so we get Jan 22 returned.

If today were 1/27 then the result of our initial COUNTIF would be 0, ie FALSE, in which case the FALSE action of the IF function would be actioned, THIRD BIT, namely:

""

ie return blank


If you ever need to work through a formula be sure to check out the "Evaluate Formula" option in XL which permits you to step through how a given formula is calculated -- it is a superb and often overlooked utility IMO and is handy for all users regardless of supposed expertise.