WorkingDays ( StartDate ; EndDate ; SaturdayIsWorkingDay )

Calculates the number of working days in an interval between StartDate and EndDate.

Be the first to rate this function Log in to vote

Federico Severin - Show more from this author
Sevesoftware Engineering
http://www.sevesoftware.it

Sample input:

1. WorkingDays ( Date ( 1 ; 1 ; 2021 ) ; Date ( 12 ; 31 ; 2021 ) ; False )
2. WorkingDays ( Date ( 1 ; 1 ; 2021 ) ; Date ( 12 ; 31 ; 2021 ) ; True )

Sample output:

1. 261
2. 313

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

/*
v1.0 
12 dec 2022 by Federico Severin

Prototype:
	WorkingDays ( StartDate ; EndDate ; SaturdayIsWorkingDay )

Description:
	Calculates the number of working days in an interval between StartDate and EndDate.

Parameters:
	StartDate: beginning of the time interval.
	EndDate: end of the time interval.
	SaturdayIsWorkingDay: set to True or 1 if saturday has to be counted as working day, 0 or empty otherwise.

E.g.:
WorkingDays ( Date ( 1 ; 1 ; 2021 ) ; Date ( 12 ; 31 ; 2021 ) ; False ) -> 261
WorkingDays ( Date ( 1 ; 1 ; 2021 ) ; Date ( 12 ; 31 ; 2021 ) ; True ) -> 313
*/


While ( 
	// [ initialVariable ] 
	[
		_CurrentDate = StartDate;
		_WorkingDaysCounter = 0
	]; 
	
	// condition 
	_CurrentDate <= EndDate; 
	
	// [ logic ] 
	[
		// +1 to the counter if the current day is a working day.
		_WorkingDaysCounter = _WorkingDaysCounter + GetAsBoolean (
			Mod ( DayOfWeek ( _CurrentDate ) ; 7 + SaturdayIsWorkingDay ) > 1
		);
		// Iteration to next day.
		_CurrentDate = GetAsDate ( _CurrentDate + 1 )
	]; 
	
	// result 
	_WorkingDaysCounter
)

v1.0
12 dec 2022 by Federico Severin

Prototype:
WorkingDays ( StartDate ; EndDate ; SaturdayIsWorkingDay )

Description:
Calculates the number of working days in an interval between StartDate and EndDate.

Parameters:
StartDate: beginning of the time interval.
EndDate: end of the time interval.
SaturdayIsWorkingDay: set to True or 1 if saturday has to be counted as working day, 0 or empty otherwise.

E.g.:
WorkingDays ( Date ( 1 ; 1 ; 2021 ) ; Date ( 12 ; 31 ; 2021 ) ; False ) -> 261
WorkingDays ( Date ( 1 ; 1 ; 2021 ) ; Date ( 12 ; 31 ; 2021 ) ; True ) -> 313

DayOfWeek() gives 7 for Saturday and 1 for Sunday. Using Mod ( DayOfWeek ( _CurrentDate ) ; 7 ) turns Saturday to 0, so if the current day is >1 it's a working day.
By adding SaturdayIsWorkingDay to the 7 in the Mod() function, nothing changes from what explained before if SaturdayIsWorkingDay is set to 0 or False. But, if set to True or 1, it turns the 7 in the Mod() to 8. This basically is like having no Mod() at all in this context, it's like evaluating:
DayOfWeek ( _CurrentDate ) > 1
Since Saturday is again a 7 and 7>1, Saturday is counted as working day.
I know, I could have used an If(), but I'm lazy and to me sounded even easier written like that.

Comments

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

WorkingDays ( StartDate ; EndDate ; SaturdayIsWorkingDay )

Comments

Support this website.

Support this website.