Brian Dunning's FileMaker Custom Functions

CountWorkingDays ( From ; To )

This helps to find no of working days in a date range including the given dates.

  Average rating: 4.3 (27 votes) Log in to vote

Saigopal Das   Saigopal Das
Mindfire Solutions
http://www.mindfiresolutions.com/

Share on Facebook Share on Twitter

  Sample input:
CountWorkingDays ( Date ( 1;5;2014 );Date ( 9;15;2015 ) )
  Sample output:
442

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

This function first calculated the no of week between the provided dates and then adds the working days of the current week of from and to dates.

If the from and to years are different , then it calculates the working days of a particular year and adds it recursively.

 

Comments

Bruce   Bruce, Redmond WA
Sep 9, 2015
Not sure this is right:
List(
CountWorkingDays( get( currentDate); get( currentDate) + 1 );
CountWorkingDays( get( currentDate); get( currentDate) + 2 );
CountWorkingDays( get( currentDate); get( currentDate) + 3 );
CountWorkingDays( get( currentDate); get( currentDate) + 4 );
CountWorkingDays( get( currentDate); get( currentDate) + 5 );
CountWorkingDays( get( currentDate); get( currentDate) + 6 );
CountWorkingDays( get( currentDate); get( currentDate) + 7 )
)

Result:
2
3
4
3
4
5
6
 
Bruce   Bruce, Redmond WA
Sep 10, 2015
To clarify: it looks like your function produces an error.

As you step through an increasing span of days, sometimes the count of working days moves backwards.
 
eos   eos, Teg
Sep 10, 2015
As mentioned in another context: this can be coded non-recursively, and a whole lot shorter:

/*
Signature: CountWorkingDays_eos ( From ; To )
Recursive: No
*/

Case (
To > From ;
Let ( [
diff = To - From ;
weeks = Div ( diff ; 7 ) ;
days = Mod ( diff ; 7 ) ;
firstWeekDay = To - days + 1 ;
dayOfWeekFirstDay = DayOfWeek ( firstWeekDay ) ;
dayList = MiddleValues ( "1¶2¶3¶4¶5¶6¶7¶1¶2¶3¶4¶5" ; dayOfWeekFirstDay ; days ) ;
weekdays = ValueCount ( FilterValues ( "2¶3¶4¶5¶6" ; dayList ) )
] ;
weeks * 5 + weekDays
)
)
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.