Prioritising Dates

[IanDouglas]

Hello all,

I have a lot of data with three input columns (End date, Break Date, Rent review) and I can't work out a formula for the output column (LEASE EVENT).

In each row, i want to know the closest date FROM today date, with the corresponding column heading to end up in the output column. A mannual example below

[TABLE="width: 468"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]End date (to)[/TD]
[TD]BreakDate[/TD]
[TD]Rent Review[/TD]
[TD]LEASE EVENT[/TD]
[/TR]
[TR]
[TD]30/06/2025[/TD]
[TD]01/07/2020[/TD]
[TD]01/07/2015[/TD]
[TD]Break Date[/TD]
[/TR]
[TR]
[TD]30/06/2026[/TD]
[TD]n/a[/TD]
[TD]01/07/2016[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]24/02/2018[/TD]
[TD]25/03/2013[/TD]
[TD]25/07/2017[/TD]
[TD]Rent Review[/TD]
[/TR]
[TR]
[TD]03/11/2019[/TD]
[TD]04/11/2015[/TD]
[TD]04/11/2015[/TD]
[TD] ?? [/TD]
[/TR]
</tbody>[/TABLE]

I need help building a formula for this.

Kind regards,

Ian[TABLE="width: 424"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Welcome to the forum.

I think you meant to say that you want the next closest data to today's date. Your sample data includes dates that are closer to today but have already passed.

This works, but it is admittedly bulky. Copy D2 down as necessary.

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]End date (to)[/TD]
[TD="bgcolor: #FFF2CC"]BreakDate[/TD]
[TD="bgcolor: #FFF2CC"]Rent Review[/TD]
[TD="bgcolor: #FFF2CC"]LEASE EVENT[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6/30/2025[/TD]
[TD="align: right"]7/1/2020[/TD]
[TD="align: right"]7/1/2015[/TD]
[TD="bgcolor: #E2EFDA"]BreakDate[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6/30/2026[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/1/2016[/TD]
[TD="bgcolor: #E2EFDA"]End date (to)[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2/24/2018[/TD]
[TD="align: right"]3/25/2013[/TD]
[TD="align: right"]7/25/2017[/TD]
[TD="bgcolor: #E2EFDA"]Rent Review[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11/3/2019[/TD]
[TD="align: right"]11/4/2015[/TD]
[TD="align: right"]11/4/2015[/TD]
[TD="bgcolor: #E2EFDA"]End date (to)[/TD]

</tbody>

Sheet27​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=INDEX($A$1:$C$1,,MAX((MIN(IF((A2:C2-TODAY())>0,(A2:C2-TODAY()),10^30))=(A2:C2-TODAY()))*(COLUMN(A2:C2)-COLUMN(A2)+1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]

 

[IanDouglas]

Welcome to the forum.

I think you meant to say that you want the next closest data to today's date. Your sample data includes dates that are closer to today but have already passed.

This works, but it is admittedly bulky. Copy D2 down as necessary.

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]End date (to)[/TD]
[TD="bgcolor: #FFF2CC"]BreakDate[/TD]
[TD="bgcolor: #FFF2CC"]Rent Review[/TD]
[TD="bgcolor: #FFF2CC"]LEASE EVENT[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6/30/2025[/TD]
[TD="align: right"]7/1/2020[/TD]
[TD="align: right"]7/1/2015[/TD]
[TD="bgcolor: #E2EFDA"]BreakDate[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6/30/2026[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/1/2016[/TD]
[TD="bgcolor: #E2EFDA"]End date (to)[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2/24/2018[/TD]
[TD="align: right"]3/25/2013[/TD]
[TD="align: right"]7/25/2017[/TD]
[TD="bgcolor: #E2EFDA"]Rent Review[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11/3/2019[/TD]
[TD="align: right"]11/4/2015[/TD]
[TD="align: right"]11/4/2015[/TD]
[TD="bgcolor: #E2EFDA"]End date (to)[/TD]

</tbody>

Sheet27​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=INDEX($A$1:$C$1,,MAX((MIN(IF((A2:C2-TODAY())>0,(A2:C2-TODAY()),10^30))=(A2:C2-TODAY()))*(COLUMN(A2:C2)-COLUMN(A2)+1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]
[

Worked like a charm! Thankyou very much for our help DrSteele]