VBA function find closest date from list

G

gero92

New Member
Joined
Apr 15, 2018
Messages
10
Hello everybody,

I am writing a vba function that assigns a value of 10 * a probability to each calendar date in the future, only if that date is within -5<date<5 the next closest date from the EVENTS list (on the left below the excel example). So if I am on the 17/04/2018 and the closest EVENTS date is the 18/04/2018, the function will assign a value of 5 (10*EVENTS probability). The 15/05/2018 will have a value of 6 ( closest EVENT 18/05/2018, prob 60%). A date like 25/04/2018 will have a value of 0 as it is not within 5 days of any EVENTS date. I can't figure out how to use the correct probability in the VBA function. Below what I wrote. I am an absolute beginner of VBA but I am learning so much from this forum. Any very simple solution to this would be soon much appreciated guys!:)

Function IncreaseT(t As Integer, prob As Range)
' t is the calendar date
' prob is the probability assigned to each EVENTS date


If t > 5 Or t < -5 Then
IncreaseT = 0
Else: IncreaseT = 10 * prob
Exit Function


[TABLE="width: 536"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD]EVENTS[/TD]
[TD]Probability[/TD]
[TD][/TD]
[TD]Calendar[/TD]
[TD]Distance from closest Event[/TD]
[/TR]
[TR]
[TD]18/04/18[/TD]
[TD]50%[/TD]
[TD][/TD]
[TD]15/04/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18/05/18[/TD]
[TD]60%[/TD]
[TD][/TD]
[TD]16/04/18[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]20/08/18[/TD]
[TD]50%[/TD]
[TD][/TD]
[TD]17/04/18[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]02/11/18[/TD]
[TD]80%[/TD]
[TD][/TD]
[TD]18/04/18[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]24/02/19[/TD]
[TD]20%[/TD]
[TD][/TD]
[TD]19/04/18[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]29/05/19[/TD]
[TD]30%[/TD]
[TD][/TD]
[TD]20/04/18[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21/04/18[/TD]
[TD]-3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22/04/18[/TD]
[TD]-4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23/04/18[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]24/04/18[/TD]
[TD]-6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25/04/18[/TD]
[TD]-7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]26/04/18[/TD]
[TD]-8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]27/04/18[/TD]
[TD]-9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]28/04/18[/TD]
[TD]-10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]29/04/18[/TD]
[TD]-11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30/04/18[/TD]
[TD]-12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/05/18[/TD]
[TD]-13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]02/05/18[/TD]
[TD]-14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]03/05/18[/TD]
[TD]-15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/05/18[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]05/05/18[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]06/05/18[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]07/05/18[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08/05/18[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]09/05/18[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10/05/18[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11/05/18[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/05/18[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13/05/18[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14/05/18[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15/05/18[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16/05/18[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17/05/18[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]18/05/18[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]19/05/18[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20/05/18[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21/05/18[/TD]
[TD]-3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22/05/18[/TD]
[TD]-4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23/05/18[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]24/05/18[/TD]
[TD]-6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25/05/18[/TD]
[TD]-7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]26/05/18[/TD]
[TD]-8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]27/05/18[/TD]
[TD]-9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]28/05/18[/TD]
[TD]-10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]29/05/18[/TD]
[TD]-11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30/05/18[/TD]
[TD]-12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]31/05/18[/TD]
[TD]-13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01/06/18[/TD]
[TD]-14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]02/06/18[/TD]
[TD]-15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]03/06/18[/TD]
[TD]-16[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
The initial explanation was cut as too long I suppose. The thing is that the probability in the vba function has to be the one assigned in the excel to each date in the list. So for the calendar date ex. 15/05/2018 I want the prob to be 60% (closest to 18/05/2018 date in EVENT list), while for let's say the 25/08/2018 the prob will be 50% (closest the third date in EVENT). The t in the vba function is the distance of each calendar date to the closest date in the EVENT list from excel. Hope it is clearer now! Anybody who could help please?
 
Upvote 0
How about just a formula?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td="bgcolor:#F3F3F3"]
EVENTS​
[/td][td="bgcolor:#F3F3F3"]
Probability​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td="bgcolor:#F3F3F3"]
Calendar​
[/td][td="bgcolor:#F3F3F3"]
Prob​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td]
18 Apr 2018​
[/td][td]
50%​
[/td][td][/td][td]
15 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
50%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]
18 May 2018​
[/td][td]
60%​
[/td][td][/td][td]
16 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
50%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]
20 Aug 2018​
[/td][td]
50%​
[/td][td][/td][td]
17 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
50%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]
02 Nov 2018​
[/td][td]
80%​
[/td][td][/td][td]
18 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
50%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]
24 Feb 2019​
[/td][td]
20%​
[/td][td][/td][td]
19 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
50%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]
29 May 2019​
[/td][td]
30%​
[/td][td][/td][td]
20 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
50%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td][/td][td][/td][td][/td][td]
21 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
50%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td][/td][td][/td][td][/td][td]
22 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
50%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td][/td][td][/td][td][/td][td]
23 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
50%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td][/td][td][/td][td][/td][td]
24 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td][/td][td][/td][td][/td][td]
25 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
13​
[/td][td][/td][td][/td][td][/td][td]
26 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
14​
[/td][td][/td][td][/td][td][/td][td]
27 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
15​
[/td][td][/td][td][/td][td][/td][td]
28 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
16​
[/td][td][/td][td][/td][td][/td][td]
29 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
17​
[/td][td][/td][td][/td][td][/td][td]
30 Apr 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
18​
[/td][td][/td][td][/td][td][/td][td]
01 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
19​
[/td][td][/td][td][/td][td][/td][td]
02 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
20​
[/td][td][/td][td][/td][td][/td][td]
03 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
21​
[/td][td][/td][td][/td][td][/td][td]
04 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
22​
[/td][td][/td][td][/td][td][/td][td]
05 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
23​
[/td][td][/td][td][/td][td][/td][td]
06 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
24​
[/td][td][/td][td][/td][td][/td][td]
07 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
25​
[/td][td][/td][td][/td][td][/td][td]
08 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
26​
[/td][td][/td][td][/td][td][/td][td]
09 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
27​
[/td][td][/td][td][/td][td][/td][td]
10 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
28​
[/td][td][/td][td][/td][td][/td][td]
11 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
29​
[/td][td][/td][td][/td][td][/td][td]
12 May 2018​
[/td][td="bgcolor:#CCFFCC"]
0%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
30​
[/td][td][/td][td][/td][td][/td][td]
13 May 2018​
[/td][td="bgcolor:#CCFFCC"]
60%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
31​
[/td][td][/td][td][/td][td][/td][td]
14 May 2018​
[/td][td="bgcolor:#CCFFCC"]
60%​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
32​
[/td][td][/td][td][/td][td][/td][td]
15 May 2018​
[/td][td="bgcolor:#CCFFCC"]
60%​
[/td][/tr]
[/table]

The array formula in E2 and copied down is

Code: 
=IF(ABS(INDEX($A$2:$A$7, MATCH(MIN(ABS($A$2:$A$7 - D2)), ABS($A$2:$A$7 - D2), 0)) - D2) > 5, 0, 
        INDEX($B$2:$B$7, MATCH(MIN(ABS($A$2:$A$7 - D2)), ABS($A$2:$A$7 - D2), 0)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
167
SFCChase
SFCChase
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
275
Raj1979
R
A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
L
  • Question Question
Determine IF "KPT Met" or "KPI Not Met"
Replies
3
Views
231
StephenCrump
S

Forum statistics

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

Share this page

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