CountWorkingDays ( )

Function stats

Average user rating
496
398
9999
Support
FileMaker 7.0 +
Date posted
19 July 2016
Version
1
Recursive function
No

Author Info
 Cristos L-C

7 functions

Average Rating None

author_avatar



 

Function overview

Prototype

CountWorkingDays  ( DateStart;   DateEnd )


Parameters

DateStart  Beginning of date range


DateEnd  End of date range


Description

Tags:  working days   weekdays  

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

Examples

Sample input

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


Sample output

$example1 = 2
$example2 = -2

 

Function code

/******
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

*****/

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 )
; ~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

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

 

 

 

 

 

 

 

Top Tags

Text Parsing  (33)
List  (31)
Date  (28)
XML  (26)
Format  (23)
Sql  (22)
Dev  (20)
Debug  (17)
Interface  (15)
Layout  (15)
Text  (14)
Variables  (12)
Layout Objects  (11)
Filter  (11)
Design  (10)
Array  (7)