AddWorkingDays_cf ( DateInput ; WorkingDaysToAdd ; MaxWhileIterations )

Adds a number of working days to a given date. A separate Table with working days is required with the customers solution, since WorkDays may vary per Country, Canton etc.

Be the first to rate this function Log in to vote

Alexis Gehrt - Show more from this author
Database Designs
https://www.database-designs.ch/

Sample input:

AddWorkingDays_cf ( Date ( 6 ; 1 ; 2026 ) ; 3 ; 99 )

Sample output:

June 5th 2026 (Because thursday is feast of Corpus Christi in some areas)

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

/* 
   Custom Function: AddWorkingDays_cf (DateInput ; WorkingDaysToAdd ; MaxWhileIterations)

   Description:     Adds a number of working days to a given date. A separate Table with working days is required with the customers solution, since WorkDays may vary per Country, Canton etc.

   Parameters:      DateInput:         The starting date
                    WorkingDaysToAdd:  The number of days to add, but only counting workdays in the table
                    MaxWhileIterations: A FileMaker "hang/slow responsivenes" Prevention *

   Author:          Alexis Gehrt   alexis@database-designs.ch
   Version:         Jun 2nd, 2026 - First Release 1.0

   Sample Input:    AddWorkingDays_cf ( Date ( 6 ; 1 ; 2026 ) ; 2 ; 99 ) -> June 3rd 2026
                    AddWorkingDays_cf ( Date ( 6 ; 1 ; 2026 ) ; 3 ; 99 ) -> June 5th 2026 (Because thursday is feast of Corpus Christi in some areas)

This custom function assumes that you have a separate Table within your FileMaker solution in which for each day of the year there is an entry if a particular date is a working day or if it is a holiday. The SQL Statement in the While Loop requires a field in the Workdays Table that returns a 1, if a given date is a working day. The while loops until enough working days were added.

Sample Table: Date M/D/YYYY ; Working Day ; Holiday
1/1/2026 ; 0 ; 1   // Jan 1st Holiday
1/2/2026 ; 1 ; 0   // Some work on Jan 2nd

1/3/2026 ; 0 ; 1   // Saturday
1/4/2026 ; 0 ; 1   // Sunday
1/5/2026 ; 1 ; 0   // Monday
1/6/2026 ; 1 ; 0   // Tuesday

Using a SQL to query for the workday makes the Custom Function context independent across the solution.
 
* CAUTION: If the customer does not prefill the Workdays Table by the end of the year. The SQL will not report any working day and therefore the result will be ? - but only after the 32000 While Loop Limit. Running into this situation, FileMaker seems to hang and become non responsive. This is where the variable 'MaxWhileIterations' comes to play. Pass a reasonable number. My customer for instance wanted to add Workdays for shipping, so I chose 30 to end the loop quickly.

*/
 
While (
    [
        ~currentDate = DateInput ;
         ~daysAdded = 0
    ];

         ~daysAdded < WorkingDaysToAdd and  ~daysAdded < MaxWhileIterations ;
        [
            ~currentDate = ~currentDate + 1 ;
             ~daysAdded =  ~daysAdded + ExecuteSQL (  "SELECT Arbeitstag FROM Jahrestabelle WHERE Datum = ?" ;  "" ; "" ; Day ( ~currentDate ) & "/" & Month ( ~currentDate ) & "/" & Year ( ~currentDate ) ) ;
            result = ~currentDate
        ];
        result
    )

This custom function assumes that you have a separate Table within your FileMaker solution in which for each day of the year there is an entry if a particular date is a working day or if it is a holiday. The SQL Statement in the While Loop requires a field in the Workdays Table that returns a 1, if a given date is a working day. The while loops until enough working days were added.

Sample Table: Date M/D/YYYY ; Working Day ; Holiday
1/1/2026 ; 0 ; 1 // Jan 1st Holiday
1/2/2026 ; 1 ; 0 // Some work on Jan 2nd

1/3/2026 ; 0 ; 1 // Saturday
1/4/2026 ; 0 ; 1 // Sunday
1/5/2026 ; 1 ; 0 // Monday
1/6/2026 ; 1 ; 0 // Tuesday

Using a SQL to query for the workday makes the Custom Function context independent across the solution.

CAUTION: If the customer does not prefill the Workdays Table by the end of the year. The SQL will not report any working day and therefore the result will be ? - but only after the 32000 While Loop Limit. Running into this situation, FileMaker seems to hang and become non responsive. This is where the variable 'MaxWhileIterations' comes to play. Pass a reasonable number. My customer for instance wanted to add Workdays for shipping, so I chose 30 to end the loop quickly.

Comments

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

AddWorkingDays_cf ( DateInput ; WorkingDaysToAdd ; MaxWhileIterations )

Comments

Support this website.

Support this website.