Formula to find next date in list

[SDowse]

Hi,

In my spreadsheet I have a sheet called "Stats" with various tables on it, on of which is a list of dates (header is in W1, values are in W2:W14). The dates are calculated by the first date being entered manually by me (just made the list so the first date is today) and the remainder being +14 of the date in the cell above.

What I want is a formula that can work out when the next date in the list is...so, for example, now it would show 18th July as that is the next date. The only way I can think of is doing a string of IF's, but there must be a simpler way of doing it without checking every value in the list?

Any ideas?

 

[jono19872006]

I wasnt sure what you wanted the "next date" to be ie after todays or after one in a cell reference?
substitute as necessery
note this formula will need to be entered by selecting control, alt, return rather then the normal return key

=MAX(IF(W2:W8>TODAY(),W2:W8,1000))

 

[Robert Mika]

Actullay that should be Control+Shift+Enter.
Not sure about the 1000 for the False.
But you were very close.

Try like this confirm Control+Shift+Enter
=MIN(IF(W2:W14>TODAY(),W2:W14))
that will return the closest day after today

 

[donfaison]

I am using tables, with dates in one column and prices in another. There might be a week with no prices, then 3 for one date, then 5 prices two days later, etc. I am trying to average the prices for each date that exists in the table (in another worksheet) and and am looking for a way to do this without array formulas. I tried using 1st Date = MIN(Table1[Date]) to get the first date and 2nd Date = Match(1st Date, Table1[Date], 1) to function to populate the next list, but was surprised that neither of these functions will work with the dates in the table. Any ideas?