SOLVED

formula that finds closest future date to today's date

Brass Contributor

Hi all,

 

I'm looking for a formula that looks in a Table's column (Contract Due Date), and finds the closest future date to today's current date so I can set up a graph that automatically shows the 12 month look ahead from todays date and ignores date's in the past. But the snag is that I am struggling to find a formula that it finds the 2nd closest future date after this, and then the 3rd and then the 4th etc when I drag the initial formula down to find to top 12 closest dates to show.

 

I found this formula:

=MAX(Table3[[#All],[Contract Due Date (Baseline)]]<TODAY())*(Table3[[#All],[Contract Due Date (Baseline)]])

and I thought it did everything I want, however unfortunately it uses the past not future date closest to today, and whilst it seems to allow you to counts backwards (as opposed to forward) by dragging the cell down, it doesn't appear to be accurate in providing the closest date in the past. 

 

I have tried switching MAX for MIN and < for > (=MIN(Table3[[#All],[Contract Due Date (Baseline)]]>TODAY())*(Table3[[#All],[Contract Due Date (Baseline)]])) but it comes back with 0 and doesn't appear to work.

 

I've also tried:

=MIN(IF(Table3[[#All],[Contract Due Date (Baseline)]]>TODAY(),Table3[[#All],[Contract Due Date (Baseline)]]))

This does find the closest future date to today's current date however when you drag that cell down, it just populates the 1st closest future date over and over again rather than just the 2nd and 3rd.

 

Does anyone have any suggestions?

1 Reply
best response confirmed by clh_1496 (Brass Contributor)
Solution

@clh_1496 

Let's say you enter the formula in K2.

 

=SMALL(IF(Table3[Contract Due Date (BaseLine)]>TODAY(),Table3[Contract Due Date (BaseLine)]),ROW(K2)ROW($K$2)+1)

 

Fill down.

1 best response

Accepted Solutions
best response confirmed by clh_1496 (Brass Contributor)
Solution

@clh_1496 

Let's say you enter the formula in K2.

 

=SMALL(IF(Table3[Contract Due Date (BaseLine)]>TODAY(),Table3[Contract Due Date (BaseLine)]),ROW(K2)ROW($K$2)+1)

 

Fill down.

View solution in original post