WorkingDateList ( StartDate ; EndDate ; HolidayList )

Returns a list of dates between StartDate and EndDate excluding any dates in HolidayList

Average rating: 2.0 (3 votes) Log in to vote

Paul Bastin
Computech IT
Computech-it.co.uk

Sample input:

WorkingDateList (01/01/2018;10/01/2018;"04/01/2018¶05/01/2018¶06/01/2018¶08/01/2018" )

Sample output:

01/01/2018
02/01/2018
03/01/2018
07/01/2018
09/01/2018
10/01/2018

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

/* 
WorkingDateList (StartDate;EndDate;HolidayList)

Returns a list of dates between StartDate and EndDate excluding any dates in HolidayList
Does not automatically exclude weekends, they must be included in HolidayList
*/

Case(

    //Check parameters are correct. Holiday list is not checked as it can be empty or out side of date range

    IsEmpty( StartDate ) or IsEmpty( EndDate );
    "Please fill in both fields.";
    StartDate > EndDate;
    "Start date must be earlier than end date.";

    // On the first loop start first recursion and set $endCheck to $holidayList without spaces or returns
    // so we can check to see if it is empty which will end the loop.

    not $LoopCount;
    Let(
        [
            $dateList = GetDatesInRange( StartDate; EndDate );
            $holidayList = HolidayList;
            $endCheck =
            Substitute( Substitute( $holidayList; " "; "" ); "¶"; "" );
            $loopCount = 1
        ];

        // This is the first recursion -- the function just calls itself,
        // with the same parameter that the user entered

        WorkingDateList( StartDate; EndDate; HolidayList )
    );

    // Once counter is set to 1 it will continue to loop until $holidayList is empty

    not IsEmpty( $endCheck );
    Let(
        [

            // Remove the first date in $holidayList from $dateList and$holidayList

            $firstVal = GetValue( $holidayList; 1 );
            $holidayList = Substitute( $holidayList; $firstVal; "" );
            $dateList = Substitute( $dateList; $firstVal; "" );

            // Tidy up $holidayList and $dateList to remove any returns left behind

            $holidayList = Substitute( $holidayList; "¶¶"; "¶" );
            $holidayList =
            If(
                Right( $holidayList; 1 ) = "¶";
                Left( $holidayList; Length( $holidaylist ) - 1 );
                $holidayList
            );
            $holidayList =
            If(
                Left( $holidayList; 1 ) = "¶";
                Right( $holidayList; Length( $holidaylist ) - 1 );
                $holidayList
            );

            $dateList = Substitute( $dateList; "¶¶"; "¶" );
            $dateList =
            If(
                Right( $dateList; 1 ) = "¶";
                Left( $dateList; Length( $dateList ) - 1 );
                $dateList
            );
            $dateList =
            If(
                Left( $dateList; 1 ) = "¶";
                Right( $dateList; Length( $dateList ) - 1 );
                $dateList
            );

            // reset $endCheck to see if we have run out of holiday dates

            $endCheck =
            Substitute( Substitute( $holidayList; " "; "" ); "¶"; "" )
        ];

        // After finishing each step, we recurse again

        WorkingDateList( StartDate; EndDate; HolidayList )
    );

    // Finished last cycle

    IsEmpty( $endCheck );
    Let(
        [
            $result = $dateList;

            // before exiting release all variables (except the result)

            $firstVal = "";
            $dateList = "";
            $holidayList = "";
            $endCheck = "";
            $loopCount = ""
        ];

        // Return the date list

        $result
    )
)

Returns a list of dates between StartDate and EndDate excluding any dates in HolidayList.
It does not exclude weekends automatically, you have to include them in the holiday list or remove them after calling the function.

NOTE: it requires another function to get the list of dates. There are several you could choose from, but I used this:

/*
GetDatesInRange (StartDate;EndDate)
Get a list of date in the range StartDtae to EndDate
*/

Case(

// Check parameters have been entered and make a sensible range

IsEmpty( StartDate ) or IsEmpty( EndDate );
"Please fill in both fields.";
StartDate > EndDate;
"Start date must be earlier than end date.";

// If we are good then return the list of dates

Case(
EndDate > StartDate;
GetDatesInRange( StartDate; EndDate - 1 ) & "¶" & EndDate;
EndDate
)
)

Comments

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

WorkingDateList ( StartDate ; EndDate ; HolidayList )

Comments

Support this website.

Support this website.