FileMaker Pro Custom Functions

/* NAME EventCount DESCRIPTION Count the number of times a repeating event will occur between two dates. PARAMETERS DateBegin: The start date of the range to be checked. DateEnd: The stop date of the range to be checked. Frequency: Text describing the length of the repetition. Supported values are Once, Yearly, Monthly, Weekly, and Daily. When: Text representing the point in the frequency arc when the event hits. The expected format depends on the type of frequency specified. EXAMPLES EventCount ( "1/1/2007" ; "6/1/2007" ; "Once" ; "5/14/1976" ) // Returns 0 EventCount ( "1/1/2007" ; "6/1/2007" ; "Once" ; "5/14/2007" ) // Returns 1 EventCount ( "1/1/2007" ; "6/1/2008" ; "Yearly" ; "5/14" ) // Returns 2 EventCount ( "1/1/2007" ; "6/1/2007" ; "Monthly" ; "1" ) // Returns 6 EventCount ( "1/1/2007" ; "6/1/2007" ; "Monthly" ; "15" ) // Returns 5 EventCount ( "1/1/2007" ; "6/1/2007" ; "Monthly" ; "0" ) // Returns 5 (0 represents the last day of the month, -1 would represent the second to last day of the month, etc.) EventCount ( "1/1/2007" ; "1/6/2007" ; "Weekly" ; "Monday" ) // Returns 1 */ Let ([ DateBegin = GetAsDate ( DateBegin ) ; DateEnd = GetAsDate ( DateEnd ) ; Frequency = Proper ( Frequency ) ; When = Case ( Frequency = "Once" ; GetAsDate ( When ) ; Frequency = "Yearly" ; GetAsText ( When ) ; Frequency = "Monthly" ; GetAsNumber ( When ) ; Frequency = "Weekly" ; GetAsText ( Proper ( When ) ) ; Frequency = "Daily" ; "" ; ) ]; Case ( // Validate params not IsValid ( GetAsDate ( DateBegin ) ) ; "?" ; not IsValid ( GetAsDate ( DateEnd ) ) ; "?" ; Frequency = "Once" and not IsValid ( GetAsDate ( When ) ) ; "?" ; Frequency = "Yearly" and not IsValid ( GetAsDate ( When & "/" & Year ( DateBegin ) ) ) ; "?" ; Frequency = "Monthly" and Int ( When ) <> When ; "?" ; Frequency = "Weekly" and PatternCount ( "SundayMondayTuesdayWednesdayThursdayFridaySaturday" ; When ) = 0 ; "?" ; Frequency <> "Daily" and When = "" ; "?" ; // Handle the non-recursive cases DateBegin > DateEnd ; 0 ; Frequency = "Daily" ; DateEnd - DateBegin + 1 ; Frequency = "Once" and ( DateBegin ≤ When ) and ( DateEnd ≥ When ) ; 1 ; // Handle the recursive cases Case ( Frequency = "Yearly" ; DateBegin = GetAsDate ( When & "/" & Year ( DateBegin ) ) ; Frequency = "Monthly" ; DateBegin = Date ( Month ( DateBegin ) + ( 1 * When < 1 ) ; When ; Year ( DateBegin ) ) ; Frequency = "Weekly" ; DayName ( DateBegin ) = When ) + EventCount ( DateBegin + 1 ; DateEnd ; Frequency ; When ) ) )
 Then copy & paste into FileMaker Advanced's Edit Custom Function window.