Here is V2 of the ClosestDate Custom Function that I created.

Concept is the same; you need a basedate you are checking and a valuelist that contains the list of dates that you are checking against. This version includes the addition of being able to limit the closest date to either before the base date, after the base date or either before or after the base date you are comparing against.

It is two functions which are:

/*

==================================================

03/12/2005 1.0 KLN Original Version

03/16/2005 1.1 KLN Added Parameter To Limit Closest To Before, After Or

Either

==================================================

PURPOSE: To find the closest date in a table to a date in a related table. This is

accomplished by capturing the related dates to a value list for parsing out.

==================================================

USER INPUTS

TheDate = The Date field in the Current Table To Find Closest To

ValueListName = Name of the Value List That Contains Related Dates

LimitedTo = Limits closest date to either before, after or either. Valid options

are Prior, Earlier, Before, -, Later, After, Next, +

VAIRABLES

gList = Value List Of Dates To Be Checked

gClosestDate = Closest Date

gNum = Number Of Related Dates As Determined From Value List

gRange = Limiting calculation to Before, After, Or Either

OTHER CUSTOM FUNCTIONS USED

ClosestTestSub which is recurvise checking all dates.

==================================================

*/

Let ([

gList = ValueListItems ( Get ( FileName ) ; ValueLIstName );

gClosestDate = GetAsDate ( "" );

gNum = If ( IsEmpty ( glist ) ; 0 ; PatternCount ( gList ; "

Here is V2 of the ClosestDate Custom Function that I created.

Concept is the same; you need a basedate you are checking and a valuelist that contains the list of dates that you are checking against. This version includes the addition of being able to limit the closest date to either before the base date, after the base date or either before or after the base date you are comparing against.

It is two functions which are:

/*

==================================================

03/12/2005 1.0 KLN Original Version

03/16/2005 1.1 KLN Added Parameter To Limit Closest To Before, After Or

Either

==================================================

PURPOSE: To find the closest date in a table to a date in a related table. This is

accomplished by capturing the related dates to a value list for parsing out.

==================================================

USER INPUTS

TheDate = The Date field in the Current Table To Find Closest To

ValueListName = Name of the Value List That Contains Related Dates

LimitedTo = Limits closest date to either before, after or either. Valid options

are Prior, Earlier, Before, -, Later, After, Next, +

VAIRABLES

gList = Value List Of Dates To Be Checked

gClosestDate = Closest Date

gNum = Number Of Related Dates As Determined From Value List

gRange = Limiting calculation to Before, After, Or Either

OTHER CUSTOM FUNCTIONS USED

ClosestTestSub which is recurvise checking all dates.

==================================================

*/

Let ([

gList = ValueListItems ( Get ( FileName ) ; ValueLIstName );

gClosestDate = GetAsDate ( "" );

gNum = If ( IsEmpty ( glist ) ; 0 ; PatternCount ( gList ; "

Here is V2 of the ClosestDate Custom Function that I created.

Concept is the same; you need a basedate you are checking and a valuelist that contains the list of dates that you are checking against. This version includes the addition of being able to limit the closest date to either before the base date, after the base date or either before or after the base date you are comparing against.

It is two functions which are:

/*

==================================================

03/12/2005 1.0 KLN Original Version

03/16/2005 1.1 KLN Added Parameter To Limit Closest To Before, After Or

Either

==================================================

PURPOSE: To find the closest date in a table to a date in a related table. This is

accomplished by capturing the related dates to a value list for parsing out.

==================================================

USER INPUTS

TheDate = The Date field in the Current Table To Find Closest To

ValueListName = Name of the Value List That Contains Related Dates

LimitedTo = Limits closest date to either before, after or either. Valid options

are Prior, Earlier, Before, -, Later, After, Next, +

VAIRABLES

gList = Value List Of Dates To Be Checked

gClosestDate = Closest Date

gNum = Number Of Related Dates As Determined From Value List

gRange = Limiting calculation to Before, After, Or Either

OTHER CUSTOM FUNCTIONS USED

ClosestTestSub which is recurvise checking all dates.

==================================================

*/

Let ([

gList = ValueListItems ( Get ( FileName ) ; ValueLIstName );

gClosestDate = GetAsDate ( "" );

gNum = If ( IsEmpty ( glist ) ; 0 ; PatternCount ( gList ; "

ClustomFunctions.fp7.zip