Copy nearest index sorted by date

smide

Board Regular
Joined
Dec 20, 2015
Messages
164
Office Version
  1. 2016
Platform
  1. Windows
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).
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
ABC
126-Sep-19
2
3122.09.2019 - 22:004
4222.09.2019 - 22:00
5324.09.2019 - 21:00
6427.09.2019 - 16:00
7529.09.2019 - 12:45
Sheet
 
Upvote 0
Try:


Book1
ABCD
126-Sep-19
2
3122.09.2019 - 22:0043
4222.09.2019 - 22:00
5324.09.2019 - 21:00
6427.09.2019 - 16:00
7529.09.2019 - 12:45
Sheet11
Cell Formulas
RangeFormula
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.
 
Upvote 0
Hello Eric.
I'm using Excel 2007. Do you have any idea what can I use instead of 'aggregate' function?
 
Upvote 0
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:

Code:
=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:

Code:
=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))
 
Upvote 0
Thank you so much for this 'adjusted' formulas. :beerchug:
 
Upvote 0
Just another shorter way to find the date:

Code:
{=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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top