Ken Newell Posted March 18, 2005 Author Share Posted March 18, 2005 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 ; " Link to comment Share on other sites More sharing options...
Ken Newell Posted March 18, 2005 Author Share Posted March 18, 2005 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 ; " Link to comment Share on other sites More sharing options...
Ken Newell Posted March 18, 2005 Share Posted March 18, 2005 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6999 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now