VBA function assign value to date based on distance

[gero92]

Hi everybody,

I have a list of dates (Events) and another list of dates that fall in between those (special events). I want to do a vba function that finds the special event in the calendar, and add to each calendar date 10 divided by the number in between the 2 event dates that contain the special event date, only if a special date is in between the 2 event dates. The function has to count the number of days between the event dates and leave the 5 days before and after the special date blank and assign the value to the other dates. I attach an example of the excel down here, the value to be assigned date is what the vba should return. The excel is dynamic so dates and number of days in between change everyday.

I have been breaking my head on this for so long, I'd be sooooo grateful if anybody could help! thank you !

Special Event	Event		Calendar	Distance	Value to be assigned
28/04/18	20/04/18		20/04/18
18/05/18	05/05/18		21/04/18	7	2.5	2.5=10 (count of days between the event dates)
20/06/18	04/06/18		22/04/18	6	2.5
02/11/18	06/07/18		23/04/18	5
24/02/19	09/09/18		24/04/18	4		Nothing here as it is 5 days before/after the special date
29/05/19	13/11/18		25/04/18	3
	17/01/19		26/04/18	2
	23/03/19		27/04/18	1
	27/05/19		28/04/18	0
	31/07/19		29/04/18	1
	04/10/19		30/04/18	2
			01/05/18	3
			02/05/18	4
			03/05/18	5
			04/05/18	6	2.5
			05/05/18	7	2.5
			06/05/18	8	0.59
			07/05/18	9	0.59
			08/05/18	10	0.59	0.59 = (10/17, 17 is the number of days between the 2 event dates)
			09/05/18	9	0.59
			10/05/18	8	0.59
			11/05/18	7	0.59
			12/05/18	6	0.59
			13/05/18	5
			14/05/18	4
			15/05/18	3
			16/05/18	2
			17/05/18	1
			18/05/18	0
			19/05/18	1
			20/05/18	2
			21/05/18	3
			22/05/18	4
			23/05/18	5
			24/05/18	6	0.59
			25/05/18	7	0.59
			26/05/18	8	0.59
			27/05/18	9	0.59
			28/05/18	10	0.59
			29/05/18	11	0.59
			30/05/18	12	0.59
			31/05/18	13	0.59
			01/06/18	14	0.59
			02/06/18	15	0.59
			03/06/18	16	0.59
			04/06/18	16
			05/06/18	15
			06/06/18	14
			07/06/18	13
			08/06/18	12
			09/06/18	11
			10/06/18	10
			11/06/18	9
			12/06/18	8
			13/06/18	7
			14/06/18	6
			15/06/18	5
			16/06/18	4
			17/06/18	3
			18/06/18	2
			19/06/18	1
			20/06/18	0
			21/06/18	1
			22/06/18	2
			23/06/18	3
			24/06/18	4
			25/06/18	5
			26/06/18	6
			27/06/18	7
			28/06/18	8
			29/06/18	9
			30/06/18	10
			01/07/18	11
			02/07/18	12
			03/07/18	13
			04/07/18	14
			05/07/18	15
			06/07/18	16
			07/07/18	17
			08/07/18	18
			09/07/18	19
			10/07/18	20
			11/07/18	21
			12/07/18	22
			13/07/18	23
			14/07/18	24
			15/07/18	25
			16/07/18	26
			17/07/18	27
			18/07/18	28
			19/07/18	29
			20/07/18	30
			21/07/18	31
			22/07/18	32
			23/07/18	33
			24/07/18	34
			25/07/18	35
			26/07/18	36
			27/07/18	37
			28/07/18	38
			29/07/18	39
			30/07/18	40
			31/07/18	41
			01/08/18	42
			02/08/18	43
			03/08/18	44
			04/08/18	45
			05/08/18	46
			06/08/18	47
			07/08/18	48
			08/08/18	49
			09/08/18	50
			10/08/18	51
			11/08/18	52
			12/08/18	53
			13/08/18	54
			14/08/18	55
			15/08/18	56
			16/08/18	57
			17/08/18	58
			18/08/18	59
			19/08/18	60
			20/08/18	61
			21/08/18	62
			22/08/18	63
			23/08/18	64
			24/08/18	65
			25/08/18	66
			26/08/18	67
			27/08/18	67

<colgroup><col><col span="4"><col><col></colgroup><tbody>
</tbody>