Look for specific date

[Nicolaf]

Hi,

I have a list of dates and would like to have a formula that gives me back the next date from today's date.

So for example list of dates below:

11-Apr-2013
11-Oct-2013
11-Apr-2013
11-Oct-2013

Let's say these dates are found in Cells G5:G8.

I would like to write a formula in cell H5 which gives me back next available value to today so in this case answer would be 11-Oct-2013.

I can put today's value in cell I5 (formula would be =today())

What is best way to do this?

Nix

[shrivallabha]

Question:
If your data is:
11-Apr-2013
11-Sep-2013
11-Oct-2013
11-Nov-2013

i.e. If there are 3 dates that fulfill the criteria then which date should be returned by the formula?

[Nicolaf]

It should be the next available so the first in line in this case 11-Sep-2013.


In my original thread the dates should be:

11-Apr-2013
11-Oct-2013
11-Apr-2014
11-Oct-2014

and starting from today the next available date is indeed 11-Oct-2013

Thanks,
Nix

[Kenneth Hobs]

This works best if you sort the dates.
=VLOOKUP(TODAY(),G5:G8,1,TRUE)

[shrivallabha]

It should be the next available so the first in line in this case 11-Sep-2013.


In my original thread the dates should be:

11-Apr-2013
11-Oct-2013
11-Apr-2014
11-Oct-2014

and starting from today the next available date is indeed 11-Oct-2013

Thanks,
Nix

With the cell formatted as date and assuming your data in A1:A4, CTRL + SHIFT + ENTER following formula:
=MIN(IF(A1:A4>TODAY(),A1:A4))
If you do it correctly Excel will wrap it with {} braces.

[Nicolaf]

Great thanks!