BusinessDays ( startDate ; endDate )

Returns the number of business days between two dates (inclusive)

Average rating: 4.1 (38 votes) Log in to vote

Jeremy Bante

https://github.com/jbante/

Sample input:

BusinessDays ( Date ( 11 ; 10 ; 2011 ) ; Date ( 11 ; 30 ; 2012 ) )

Sample output:

265

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

/**
 * =====================================
 * BusinessDays ( startDate ; endDate )
 *
 * RETURNS:
 *		The number of business days (non-holiday weekdays) falling between
 *		startDate and endDate, inclusive.
 *
 * PARAMETERS:
 *		startDate: A date
 *		endDate: A date, endDate ≥ startDate
 *
 * DEPENDENCIES:
 *		BusinessHolidays ( calendarYear )
 *
 * HISTORY:
 *		MODIFIED on 2012-02-22 by Jeremy Bante to check for valid input
 *		parameters based on a comment by Brian Rich.
 *		MODIFIED on 2011-10-19 by Jeremy Bante to correctly handle multi-year
 *		ranges.
 *		CREATED on 2011-10-18 by Jeremy Bante <http://scr.im/jbante>.
 * =====================================
 */

Case (
	IsEmpty ( startDate ) or IsEmpty ( endDate ) ; "?";	// input is invalid
	startDate > endDate ; 0 - BusinessDays ( endDate ; startDate );
/* Else */
	Let ( [
		// compare calendar years, in case range spans multiple years
		~startYear = Year ( startDate );
		~endYear = Year ( endDate );
		~splitDate = Date ( 1 ; 1 ; ~startYear + 1 ); // first 1 Jan after startDate
		~endDate = If ( ~endYear > ~startYear ; ~splitDate - 1 ; endDate );

		~startDay = DayOfWeek ( startDate );
		~endDay = DayOfWeek ( ~endDate );

		~startSaturday = startDate - ~startDay + 7;
		~endSaturday = ~endDate - ~endDay;
		~fullWeeks = Div ( ~endSaturday - ~startSaturday + 1 ; 7 );

		~leadingWeekDays =
			Choose ( ~startDay - 1 ; 5 ; 5 ; 4 ; 3 ; 2 ; 1 ; 0 );
		~trailingWeekDays =
			Choose ( ~endDay - 1 ; 0 ; 1 ; 2 ; 3 ; 4 ; 5 ; 5 );

		~holidayCalculation =
			"Let ( [ ~d = GetAsDate (\""
			& BusinessHolidays ( ~startYear )
			& "\" ) ; ~n = DayOfWeek ( ~d ) ] ; ~d ≥ GetAsDate ( startDate ) and ~d ≤ GetAsDate ( endDate ) and ~n > 1 and ~n < 7 )";
		~holidayCalculation = // Add 1 for each weekday holiday within range
			Substitute ( ~holidayCalculation;
				[ "¶" ; "\" ) ; ~n = DayOfWeek ( ~d ) ] ; ~d ≥ GetAsDate ( startDate ) and ~d ≤ GetAsDate ( endDate ) and ~n > 1 and ~n < 7 ) + Let ( [ ~d = GetAsDate ( \"" ];
				[ "startDate" ; Quote ( startDate ) ];
				[ "endDate" ; Quote ( ~endDate ) ]
			);
		~holidays = Evaluate ( ~holidayCalculation )
	];
		5 * ~fullWeeks + ~leadingWeekDays + ~trailingWeekDays - ~holidays
		+ If ( ~endYear > ~startYear ; BusinessDays ( ~splitDate ; endDate ) ; 0 )
	)
)

This function returns the number of business days (non-holiday weekdays) between startDate and endDate (including both startDate and endDate).

This function is dependent on the BusinessHolidays ( calendarYear ) function to produce a list of observed holidays in a given year.

Comments

		Brian Rich, Hampshire UK Feb 22, 2012
Business Days() imposes a severe performance hit of several seconds if the startDate parameter is empty, and returns a ? as the result. I added a case statement to a modified version of the function so that if the startDate or endDate are empty, or the endDate<startDate, the function returns a null value and doesn't try to evaluate - viz: Case( IsEmpty(startDate);""; IsEmpty(endDate);""; endDate<startDate;""; Let ... ) HTH Brian

		Jeremy Bante, USA Feb 22, 2012
Thank you for letting me know. Normally I prefer to let the consequences of bad input filter through, but performance is different. I opted to return "?" rather than null to be consistent with error behavior for FileMaker-native functions.

		aruna, india Nov 29, 2013
can i have coding for buisness days function?

		Thiyagarajan, Test Nov 12, 2018
We have weekly holidays Friday and Saturday. Where I need to change the condition in this custom function ?. Please advise.

		Michael Ghesquiere Feb 24, 2022
Inconsistent results? This is what I pass to the function: Set Variable [ $BeginningTS; Value:GetAsTimestamp ( "02/21/2022 08:25 AM" ) ] Set Variable [ $EndingTS; Value:GetAsTimestamp ( "02/22/2022 10:47 AM" ) ] // Set Variable [ $BeginningTS; Value:GetAsTimestamp ( "02/23/2022 9:28 AM" ) ] // Set Variable [ $EndingTS; Value:GetAsTimestamp ( "02/24/2022 11:33 AM" ) ] #Calculate number of business hours between two timestamps Set Variable [ $Beg_Date; Value:GetAsDate ( $BeginningTS ) ] Set Variable [ $End_Date; Value:GetAsDate ( $EndingTS ) ] Set Variable [ $BusinessDaysLapse; Value:BusinessDays ( GetAsDate ( $Beg_Date ) ; GetAsDate ( $End_Date ) ) ] Passing the two sets of variables results in two different results. The Feb 21, 22 returns 1, and the Feb 23, 24 returns 2. Can anyone explain the difference between the two executions? Thanks... Michael

		Michael Ghesquiere Feb 24, 2022
ROFL, 5 min after posting.... Feb 21 is a holiday! 2 hours wasted. Please disregard.

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

BusinessDays ( startDate ; endDate )

Comments

Support this website.

Support this website.