getting the next date based on another date

[Guest]

I have a start date in one cell and a list of dates in another part of my
worksheet. I want to get the next date after the start date from the list
of dates to populate into another cell. ie Start date is July 3 and the next
date after that is the second in the list of dates (July 12). I've tried
LOOKUP but keep on getting the first date from the list (June 12).

 

[Peo Sjoblom]

Are the dates in ascending order?

=INDEX(A2:A50,MATCH(B2,A2:A50,0)+1)

where B2 is the starting date

if not sorted you can use

=INDEX(A2:A50,MATCH(SMALL(A2:A50,COUNTIF(A2:A50,"<="&B2)+1),A2:A50,0))


the latter assumes you always want the date greater than the date in B2, the
former simply gets the next date in sorting order as long the
date in B2 is found and it is not the last date of the list


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[T. Valko]

When you say "next date" do you mean the next date that is closest to the
start date or do you mean the list dates will include the start date and you
want the next date that is physically listed after the start date?

Biff

 

[Guest]

I've tried this and I get N/A in the cell. To further clarify, my start date
is in cell H13 and is entered by the user. Value = July 3, 2007
The orientation dates are in cells A85:A92 and the values are:
June 12, 2007
July 14, 2007
August 15, 2007
September 13, 2007
October 12, 2007
November 12, 2007
December 14, 2007

I'm wanting to return the value from A85:A92 into H23 that is the next date
after the start date. For the example the returned value would be July 14,
2007. If the start date is October 22 then the returned value should be
November 12, 2007. Thanks for any additional insight you can give me.

 

[Peo Sjoblom]

This worked for me using your example

=INDEX(A85:A92,MATCH(SMALL(A85:A92,COUNTIF(A85:A92,"<="&H13)+1),A85:A92,0))

note that the H23 needs to be formatted as a date or you will get 39277


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[T. Valko]

A few keystrokes shorter:

=IF(H13<A85,A85,INDEX(A85:A92,MATCH(H13,A85:A92)+1))

The only difference is the error returned if the date in H13 is >= max date
in the table.

You formula will return #NUM! mine will return #REF!.

Or, this array formula** which will return a blank:

=IF(H13>=A92,"",MIN(IF(A85:A92>H13,A85:A92)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[Guest]

Thanks very much. They all worked great. Boss is impressed as well!