FORMULA TO LOCATE NEXT DATE

[Ellie]

Hi

I'm posting this question on behalf of a colleague, so hope I have
understood the question correctly.

He has a spreadsheet with an ascending list of dates (column D). In column
A he is wanting to show the next date.

Example - Cell D2 = 7 September (07/09/2007) and the next date in the list
(Cell D3) is 11 September (11/09/2007).

In Cell A2, he wants to place a formula showing the next date (i.e. cell
D3), allowing for the fact that on some days there may be more than 1 row
with the same date, so will all need to be show in Column A.

Could anyone make any suggestions and if not clear on the question, please
don't hesitate to come back and ask.

Many thanks in advance.

 

[Max]

One play using non-array formulas which will deliver the desired dynamic
unique listing of dates in source col E ..

Source dates assumed running in D2 down

In A2:
=IF(E2="","",IF(COUNTIF(E$2:E2,E2)>1,"",ROW()))
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(E:E,SMALL(A:A,ROWS($1:1))))
Format B2 as date. Select A2:B2, copy down to cover the max expected extent
of data in col E. Hide away col A. Col B will dynamically return the desired
unique list of dates in col E, all neatly bunched at the top.

 

[Ellie]

Thank you Max.

Unfortunately my colleague advises that I have misunderstood slightly what
he requires.

Where I stated that in the dates column, column D, there may be some
instances occurring where the same date occurs in more than one row of that
column, he has advised he is only requiring to know the first occurrence to
be shown in column A.

Hope this makes sense.

Many thanks.

Ellie

 

[Max]

Where I stated that in the dates column, column D, there may be some

instances occurring where the same date occurs in more than one row of that
column, he has advised he is only requiring to know the first occurrence to
be shown in column A.

But, but, isn't that essentially what's returned in col B in the suggested
set-up?

An example ..

Supposing Col E (source dates) contains the following dates in E2:E8

07-09-2007
11-09-2007
11-09-2007
12-09-2007
12-09-2007
12-09-2007
13-09-2007

then Col B (unique extracts of source dates) would return it as:

07-09-2007
11-09-2007
12-09-2007
13-09-2007

 

[Ellie]

Sorry Max, having just been advised the structure of the spreadsheet has
changed and in turn also the date basis of my earlier request, starting again:

Column D contains the dates, as before. Cell A1 only is to show the next
date after today's date.

Sorry for the change and hope you are able to help.

Ellie

 

[Max]

Column D contains the dates, as before.

Cell A1 only is to show the next date after today's date.

Try in A1, array-enter the formula by pressing Ctrl+Shift+Enter:
=MIN(IF(D$2$100>TODAY(),D$2$100))
Format A1 as date. Adjust the range to suit.

 

[Ellie]

Thank you, Max. Works brilliantly.

Many thanks for your patience and help and apologies for the sudden change
of requirements half way through.

Ellie

 

[Max]

welcome, Ellie. glad that worked <g>