Copy nearest index sorted by date

[smide]

Hello.


In cell B1 I have current date created with formula =today() eg. 26-Sep-2019.
In the same column starting from cell B3 (B3:B600) I have dates and time in format dd.mm.yyyy - hh:mm (example. 20.09.2019 - 21:00).

All those cells (B3:B600) are text cells. In column A there are numbers/index in ascending order.
Somehow I need to find the closest date (B3:B600) to the current date (B3) and to return the corresponding index (column A) into cell C3.

example.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]26-Sep-2019[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]22.09.2019 - 22:00[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]22.09.2019 - 22:00[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]24.09.2019 - 21:00[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]27.09.2019 - 16:00[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"] 29.09.2019 - 12:45[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

explanation: closest date is 27.09.2019 (cell B6) so index in cell C3 is 4 (A6).

 

[AhoyNC]

Maybe something like this.
This formula will return the first value if there are duplicates. It would return 1 for 22.09.2019. Does that work for you are do you need to return both 1 & 2?

This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C
1		26-Sep-19
2
3	1	22.09.2019 - 22:00	4
4	2	22.09.2019 - 22:00
5	3	24.09.2019 - 21:00
6	4	27.09.2019 - 16:00
7	5	29.09.2019 - 12:45
Sheet

 

[Eric W]

Try:

Book1
	A	B	C	D
1		26-Sep-19
2
3	1	22.09.2019 - 22:00	4	3
4	2	22.09.2019 - 22:00
5	3	24.09.2019 - 21:00
6	4	27.09.2019 - 16:00
7	5	29.09.2019 - 12:45
Sheet11

Cell Formulas
Range	Formula
B1	=TODAY()
C3	{=INDEX(A3:A600,MATCH(AGGREGATE(15,6,ABS(DATE(MID(B3:B600,7,4),MID(B3:B600,4,2),LEFT(B3:B600,2))-B1),1),ABS(DATE(MID(B3:B600,7,4),MID(B3:B600,4,2),LEFT(B3:B600,2))-B1),0))}
D3	{=INDEX(A3:A600,MATCH(AGGREGATE(15,6,ABS(DATE(MID(B3:B600,7,4),MID(B3:B600,4,2),LEFT(B3:B600,2))+TIMEVALUE(RIGHT(B3:B600,5))-B1),1),ABS(DATE(MID(B3:B600,7,4),MID(B3:B600,4,2),LEFT(B3:B600,2))+TIMEVALUE(RIGHT(B3:B600,5))-B1),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

The C3 formula disregards the time from B3:B600, and returns the value you expect. The D3 formula includes the time, and returns a different value, since 24.09.2019 - 21:00 is 27 hours away from 26-Spe-2019 (midnight), and 27.09.2019 - 16:00 is 40 hours away.

 

[smide]

Hello Eric.
I'm using Excel 2007. Do you have any idea what can I use instead of 'aggregate' function?

 

[Eric W]

I used AGGREGATE because it ignores errors, which means that I can use the entire B3:B600 range instead of limiting the range to B3:B7. Without AGGREGATE, we can check for an empty cell in B3:B600 and ignore it. So the formula would be:

=INDEX(A3:A600,MATCH(MIN(IF(B3:B600="","",ABS(DATE(MID(B3:B600,7,4),MID(B3:B600,4,2),LEFT(B3:B600,2))-B1))),IF(B3:B600="","",ABS(DATE(MID(B3:B600,7,4),MID(B3:B600,4,2),LEFT(B3:B600,2))-B1)),0))

entered with Control+Shift+Enter.

The version with the time included is:

=INDEX(A3:A600,MATCH(MIN(IF(B3:B600="","",ABS(DATE(MID(B3:B600,7,4),MID(B3:B600,4,2),LEFT(B3:B600,2))+TIMEVALUE(RIGHT(B3:B600,5))-B1))),IF(B3:B600="","",ABS(DATE(MID(B3:B600,7,4),MID(B3:B600,4,2),LEFT(B3:B600,2))+TIMEVALUE(RIGHT(B3:B600,5))-B1)),0))

 

[smide]

Thank you so much for this 'adjusted' formulas.

 

[Eric W]

Glad we could help!

 

[DanteAmor]

Just another shorter way to find the date:

{=INDEX(A3:A600,MATCH(MIN(IF(B3:B600="","",ABS([COLOR=#0000ff]LEFT(SUBSTITUTE(B3:B600,".","/"),10)+0[/COLOR]-B1))),IF(B3:B600="","",ABS([COLOR=#0000ff]LEFT(SUBSTITUTE(B3:B600,".","/"),10)+0[/COLOR]-B1)),0))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.