Distance of date from closest date in a list

[gero92]

Hello everybody,

I am trying to find a solution to a problem it's causing me many difficulties. I have a list of 7-8 dates of events, and a list of the days for the next 2 years. See the below example:

LIST OF EVENTS
18/04/2018
18/05/2018
20/08/2018
....
....
10/08/2019

CALENDAR
=today()
+1
+1
...
..
..
01/01/2020

I want to find for each day of the calendar the distance from the closest date in the EVENT list. For example today's date is the 15/04/2018, the closest date from the EVENT list is the 18/04/2018, so I want the formula to return the value 3. If I am looking at the 20/04/2018, I want the value -2 as it is 2 days after the closest date. If I am looking at the 22/05/2018 I want the formula to return -4 as it is 4 days after the closest date in the EVENT list 20/08/2019.

I tried many solutions from the forum but they don't really work on this case. I would be so grateful if somebody could help!

Thank you so much in advance.

 

[markmzz]

Hi!

Try the Array Formula below in D2 and copy down

Use Ctrl+Shift+Enter to enter the formula

=INDEX(A$2:A$8,MATCH(MIN(ABS(A$2:A$8-C2)),ABS(A$2:A$8-C2),0))-C2

	A	B	C	D	E
1	LIST OF EVENTS		CALENDAR	RESULT
2	18/04/2018		15/04/2018	3
3	18/05/2018		16/04/2018	2
4	20/08/2018		17/04/2018	1
5	22/11/2018		18/04/2018	0
6	24/02/2019		19/04/2018	-1
7	29/05/2019		20/04/2018	-2
8	10/08/2019		21/04/2018	-3
9			22/04/2018	-4
31			14/05/2018	4
32			15/05/2018	3
33			16/05/2018	2
34			17/05/2018	1
35			18/05/2018	0
36			19/05/2018	-1
37			20/05/2018	-2
38			21/05/2018	-3
39			22/05/2018	-4
40			23/05/2018	-5
125			16/08/2018	4
126			17/08/2018	3
127			18/08/2018	2
128			19/08/2018	1
129			20/08/2018	0
130			21/08/2018	-1
131			22/08/2018	-2
132			23/08/2018	-3
133			24/08/2018	-4
****	**************	**	***********	********	**

<tbody>
</tbody>

Markmzz

 

[Aladin Akyurek]

In E1 ontrol+shift+enter, not just enter:

=INDEX($A$1:$A$8,MATCH(MIN(ABS($A$1:$A$8-D1)),ABS($A$1:$A$8-D1),0))-D1

where D1 houses a date like 2018-04-15 (today's date) and A1:A8 an event list of dates.

 

[markmzz]

Hi!

Try the Array Formula below in D2 and copy down

Use Ctrl+Shift+Enter to enter the formula

=INDEX(A$2:A$8,MATCH(MIN(ABS(A$2:A$8-C2)),ABS(A$2:A$8-C2),0))-C2

Markmzz

One more option (a smalll modification in my first formula) in D2 and copy down:

Use only Enter to enter the formula

=INDEX(A$2:A$8,MATCH(MIN(INDEX(ABS(A$2:A$8-C2),)),INDEX(ABS(A$2:A$8-C2),),0))-C2

Markmzz

 

[gero92]

Both solutions work thank you so much to both of you guys !!!! You saved my day