CountWeekday.cf ( StartDate ; EndDate ; CustomIgnoreList_as_ddmmyyyy )

Counts the number of weekdays between two dates

Average rating: 4.4 (31 votes) Log in to vote

Jonathan Jeffery
igeek
http://www.igeek.co.uk

Sample input:

CountWeekday.cf ( get(currentdate) ; date( 12; 31 ; 2013 ) ; "01012013¶29032013¶01042013¶06052013¶27052013¶26082013¶25122013¶26122013" )

Sample output:

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

/*
CountWeekday.cf ( StartDate ; EndDate ; CustomIgnoreList_as_ddmmyyyy )
jej@igeek.co.uk 25/09/2012

Returns the number of weekdays between two dates, ignoring the dates specified on the custom list (these can be, for instance, bank holidays or school vacation dates)

*/

Let([

§ThisDate = GetAsDate ( StartDate ) ;
§EndDate = GetAsDate ( EndDate ) ;

// check span 
§Last = 
If(
§ThisDate >= §EndDate ; 1;
0
);

// check for Custom Ignore Date
§ThisDate_Formated = Right( "0" & Day( §ThisDate );  2 )  & Right( "0" & Month( §ThisDate ); 2 )  & Year( §ThisDate  );
§OK = 
If(
PatternCount (  ¶ & CustomIgnoreList_as_ddmmyyyy  & ¶ ;  ¶ & §ThisDate_Formated  & ¶ ) ; 0 ;
1);

// check for Sat/Sun 
$$cfCount  = 
Case(
DayOfWeek ( StartDate )  > 1 and
DayOfWeek ( StartDate )  < 7 and 
§OK ; $$cfCount  + 1 ;

$$cfCount 
);

result = 
If(
§Last ; $$cfCount ;
CountWeekday.cf( §ThisDate + 1 ; §EndDate ; CustomIgnoreList_as_ddmmyyyy )
);

// tidy up
$$cfCount = ""

];

result

)

This function returns the number of weekdays between two dates, ignoring the dates specified on the custom list (these can be, for instance, bank holidays or school vacation dates).

For exmple, CountWeekday.cf ( get(currentdate) ; date( 12; 31 ; 2013 ) ; "01012013¶29032013¶01042013¶06052013¶27052013¶26082013¶25122013¶26122013" ) returns the number of weekdays between today and the end of 2013, minus UK bank holidays.

Comments

		PJ Oct 26, 2018
Hi Jonathan. Tried to input this function into FMP12 in custom functions and it wouldn't have it! 7 lines from the bottom would highlight the first forward facing bracket and claim "...was expecting an operator"... Any small changes you know of that might make this work? Many thanks if you do... :-)

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

CountWeekday.cf ( StartDate ; EndDate ; CustomIgnoreList_as_ddmmyyyy )

Comments

Support this website.

Support this website.