workdayOffset ( startDate ; offset ; holidaySerialArray )

Calculates a date offset by a given number of business days (skipping weekends and holidays in holidaySerialArray) using a While() loop for efficient forward or backward calculation.

Be the first to rate this function Log in to vote

David Schellenberg - Show more from this author

Sample input:

workdayOffset ( Date ( 12;31;2025 ) ; 4 ; "12312026¶12312027¶12312028¶12312029" )

Sample output:

1/6/2026

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

/*
	workdayOffset ( startDate ; offset ; holidaySerialArray )

	Description:
	Calculates a date offset by a specified number of business days, using a While() loop
	for performance and clarity. Business days exclude weekends (Saturday/Sunday) and
	any holiday dates contained in `holidaySerialArray`. The function supports both 
	forward and backward offsets.

	Parameters:
	- startDate : The date to begin calculation.
	- offset : The number of business days to add (positive) or subtract (negative).
	- holidaySerialArray : A return-delimited list of serial date values representing holidays.

	Notes:
	- Week starts on Sunday (1) and ends on Saturday (7).
	- Uses GetAsNumber() for internal date comparison.
	- Skips up to 300 iterations for safety to avoid infinite loops.

	Version: 2.0 : david schellenberg
	Last Updated: 2025-10-23
*/

While ([ 
	
	date_start = startDate;
	offset = offset ;
	step = Case(offset ≥ 0; 1 ; -1);

	hoidays_serial_array = 
	holidaySerialArray ;

	valid = not IsEmpty ( date_start ) + IsValid ( date_start ) + not IsEmpty ( offset ) = 3 ; 

	max = Abs(offset) ;

	i = 0 ; 
	arr = ""  

	] ; 

	(ValueCount ( arr ) < max or i = 300 ) and valid = True ; 

	[
		i = i + 1 ; 
		dte = date_start + i * step ; 
		dte_serial = GetAsNumber ( dte ) ; 

		is_workday = 
		Case(
	
			// is date a Saturday (7) or Sunday (1)?
			DayOfWeek ( dte ) = 1 or DayOfWeek ( dte ) = 7;
			0 ;
	
			// is the date in the HOLIDAYS array?
			not IsEmpty ( FilterValues ( dte_serial ; hoidays_serial_array ) );
			0 ;
	
			// else it's a business day
			1
		);

		value = Case ( is_workday ; dte ) ;
		arr = List ( arr ; value )

	] ;

	Case ( valid = True ; GetValue ( arr ; Abs ( offset ) ) ; "err check values" )

)

Calculates a date offset by a specified number of business days, using a While() loop for performance and clarity. Business days exclude weekends (Saturday/Sunday) and any holiday dates contained in `holidaySerialArray`. The function supports both forward and backward offsets.

Comments

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

workdayOffset ( startDate ; offset ; holidaySerialArray )

Comments

Support this website.

Support this website.