PDA

View Full Version : [SOLVED] Look for specific date



Nicolaf
08-22-2013, 03:01 AM
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

:think::think:

shrivallabha
08-22-2013, 08:42 AM
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
08-22-2013, 09:35 AM
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
08-22-2013, 11:07 AM
This works best if you sort the dates.
=VLOOKUP(TODAY(),G5:G8,1,TRUE)

shrivallabha
08-23-2013, 07:24 AM
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
08-23-2013, 09:11 AM
Great thanks!


:hi::hi: