CountWorkingDays ( DateStart ; DateEnd )

Returns the number of working days between two dates.

Average rating: 4.5 (13 votes) Log in to vote

Cristos Lianides-Chin - Show more from this author
Codence
https://www.codence.com

Sample input:

$example1 = CountWorkingDays ( "7/18/2016" ; "7/20/2016" )
$example2 = CountWorkingDays ( "7/20/2016" ; "7/18/2016" )

Sample output:

$example1 = 3
$example2 = -3

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

/******
    CountWorkingDays ( DateStart ; DateEnd )

    Returns the number of working days between two dates: 
        * if DateStart is BEFORE DateEnd, returns a positive number 
        * if DateStart is AFTER DateEnd, returns a negative number

    CHANGELOG
    - v1.1 | 2024-08-08 | cristos.lianides-chin@codence.com
      - Fixed omission of first day in date range, resulting in an undercount
*****/

Let (
[   ~dateStart = GetAsDate ( DateStart )
;   ~dateEnd = GetAsDate ( DateEnd )

/** Use a negative multiplier if DateStart is AFTER DateEnd. **/
;   ~inverter = If ( ~dateStart > ~dateEnd ; -1 ; 1 )

/**  Figure out how many weeks and days are between the start and end dates. **/
;   ~diff = Abs ( ~dateEnd - ~dateStart ) + 1 // +1 to include the first day
;   ~numWeeks = Int ( ~diff / 7 )
;   ~numDays = Mod ( ~diff ; 7 )

/**  Figure out what DayOfWeek the first weekday is, so we can make sure we don't
    count weekend days when counting the ~numDays (i.e., don't count Saturday or Sunday if we have 6 days left over). **/
;   ~futureDate = Case
    (   ~dateStart > ~dateEnd 
    ;       ~dateStart
    /*  ELSE */
    ;       ~dateEnd
    )
;   ~firstWeekdayDate = GetAsDate ( ~futureDate - ~numDays + 1 )
;   ~firstWeekdayDayNum = DayOfWeek ( ~firstWeekdayDate )

/**  Make sure we omit "loose" weekend days (i.e., not accounted for by multiplying out the weeks).
    Filtering technique based on a comment by "eos, Teg" in the thread at https://www.briandunning.com/cf/1809. **/
;   ~listAllDayNums = List ( 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 1 ; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ) /** List two weeks' worth of DayNums, in case we have i.e., 6 days starting on Friday. **/
;   ~listAllWorkingdayDayNums = List ( 2 ; 3 ; 4 ; 5 ; 6 ) /** The days of the week which should be counted as "working days". **/

;   ~listLooseWorkdayDayNums = MiddleValues ( ~listAllDayNums ; ~firstWeekdayDayNum ; ~numDays )
;   ~listLooseWorkdays = FilterValues ( ~listAllWorkingdayDayNums ; ~listLooseWorkdayDayNums )
;   ~countLooseWorkdays = ValueCount ( ~listLooseWorkdays )

/** Make sure we are using a negative number if DateStart is AFTER DateEnd. **/
;   ~countLooseWorkdays = ~countLooseWorkdays

;   ~workdaysPerWeek = 5
;   ~countWorkDays = GetAsNumber ( (~workdaysPerWeek * ~numWeeks ) + ~countLooseWorkdays )

;   ~value = ~inverter * ~countWorkDays
]
;   ~value
)

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/398

    Prototype: CountWorkingDays( DateStart; DateEnd )
    Function Author: Cristos L-C (http://www.fmfunctions.com/mid/496)
    Last updated: 19 July 2016
    Version: 1

*/
// ===================================

Returns the number of working days between two dates:
* if DateStart is BEFORE DateEnd, returns a positive number
* if DateStart is AFTER DateEnd, returns a negative number

Comments

		Helder Moreira Aug 8, 2024
It is missing 1 day. example: from 22/07/2024 to 02/08/2024 is counting 9 days. Am I wrong? how can I fixe it?

		Cristos Lianides-Chin, Codence Aug 8, 2024
@Helder thanks for the bug report! I've updated the function so that it's tallying the first day correctly. Please use v1.1, above.

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

CountWorkingDays ( DateStart ; DateEnd )

Comments

Support this website.

Support this website.