# Finding the closest future date from Today from a list of dates

1. ## Finding the closest future date from Today from a list of dates

Hi,

I am trying to find a function that work sucessfully to find the next date from a list of future set dates.

For example, i have a list of significant dates: 01/07/2008, 14/07/2008, 29/07/2008, 12/08/2008. Using todays date I want to retrieve the closest date to today but in the future. So today is 18/07/2008, so the next date in that list is 29/07/2008.

I do have a function that shows this.
=MIN(IF(Lookups!\$D\$2:\$D\$213>TODAY(),Lookups!\$D\$2:\$D\$213))

(D2:D213 is my list of significant date)

I have this as an array (ctrl, shift, enter)

It works fine until i save and reopen the workbook. Then, the function looses it's working as an 'array-ness' so returns the incorrect value.

I am using a workbook that is shared, if that makes any difference. Also, I know array functions are supposed to have { } once C,S,E are pressed but it doesn't seem to be the case (despite it working until re-opened)

Could someone help as to why this is happening or perhaps suggest an alternative function to give the same results?

Thanks.

2. May be
=SMALL(IF((\$d\$2:\$d\$213>TODAY()),\$d\$2:\$d\$213),1)
It is an array formula
Best wishes
Sreedhar

3. thanks,

Again, it works as with my original formula. However, i think the main problem here is that when i re-open the workbook the 'array' bit of the formula is lost. If i re-select the formula and press ctrl, shift, enter, it works again.

Is there anyway to keep this function when the workbook is re-opened?

4. PROBLEM SOLVED.

I think it was because I created the array when it was a shared workbook.

Making the workbook un-shared, creating the array function, then saving as a shared workbook again has seemed to solve the problem.

Thanks.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1