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


[TABLE="class: grid, width: 330"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]LIST OF EVENTS[/TD]
[TD][/TD]
[TD]CALENDAR[/TD]
[TD]RESULT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]18/04/2018[/TD]
[TD][/TD]
[TD="align: right"]15/04/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]18/05/2018[/TD]
[TD][/TD]
[TD="align: right"]16/04/2018[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]20/08/2018[/TD]
[TD][/TD]
[TD="align: right"]17/04/2018[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]22/11/2018[/TD]
[TD][/TD]
[TD="align: right"]18/04/2018[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]24/02/2019[/TD]
[TD][/TD]
[TD="align: right"]19/04/2018[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]29/05/2019[/TD]
[TD][/TD]
[TD="align: right"]20/04/2018[/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]10/08/2019[/TD]
[TD][/TD]
[TD="align: right"]21/04/2018[/TD]
[TD="align: right"]-3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22/04/2018[/TD]
[TD="align: right"]-4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14/05/2018[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15/05/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16/05/2018[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17/05/2018[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/05/2018[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19/05/2018[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20/05/2018[/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21/05/2018[/TD]
[TD="align: right"]-3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22/05/2018[/TD]
[TD="align: right"]-4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23/05/2018[/TD]
[TD="align: right"]-5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16/08/2018[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]126[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17/08/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]127[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/08/2018[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]128[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19/08/2018[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]129[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20/08/2018[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]130[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21/08/2018[/TD]
[TD="align: right"]-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22/08/2018[/TD]
[TD="align: right"]-2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]132[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23/08/2018[/TD]
[TD="align: right"]-3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]133[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24/08/2018[/TD]
[TD="align: right"]-4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]**************[/TD]
[TD]**[/TD]
[TD]***********[/TD]
[TD]********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


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