EventCount ( DateBegin ; DateEnd ; Frequency ; When )

Counts the number of times a repeating event will occur between two dates.

Average rating: 4.3 (40 votes) Log in to vote

Jonathan Stark - Show more from this author
Jonathan Stark Consulting
http://jonathanstark.com/

Sample input:

CountEvents ( "1/1/2007" ; "6/1/2007" ; "Once" ; "5/14/1976" ) // Returns 0
CountEvents ( "1/1/2007" ; "6/1/2007" ; "Once" ; "5/14/2007" ) // Returns 1
CountEvents ( "1/1/2007" ; "6/1/2008" ; "Yearly" ; "5/14" ) // Returns 2
CountEvents ( "1/1/2007" ; "6/1/2007" ; "Monthly" ; "1" ) // Returns 6
CountEvents ( "1/1/2007" ; "6/1/2007" ; "Monthly" ; "15" ) // Returns 5
CountEvents ( "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.)
CountEvents ( "1/1/2007" ; "1/6/2007" ; "Weekly" ; "Monday" ) // Returns 1

Sample output:

Count the number of times a repeating event will occur between two dates.

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*

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 )
    )
)

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.

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

EventCount ( DateBegin ; DateEnd ; Frequency ; When )

Comments

Support this website.

Support this website.