AddWorkDays ( startdate ; workdays ; daystoadd )

Add workdays or schedule-days to a certain startdate

Be the first to rate this function Log in to vote

Menno van Beek - Show more from this author
Van Beek Zakelijke Software
https://www.vbzs.nl

Sample input:

AddWorkDays ( Date ( 1 ; 4 ; 2022 ) ; "0111110" ; 5 )

Sample output:

01/11/2022

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

/* 
################################################################## 
# 
#  AddWorkDays function
#  Format: 	AddWorkDays ( startdate ; workdays ; daystoadd ) 
# 
#  Parameters:
#  startdate: 	Any valid date from a field or function
#  workdays: 	The workingdays of a week starting from Sunday. 
#  		1 represents a workday, 0 a non-workday. 
#  daystoadd: 	Number of workdays to add
# 
#  Data returned: 
#  Date
# 
#  Description: 
#  Add workdays to a date and skip weekends or schedule-free days by setting 
#  a string that represnt the workdays. If no workdays are set, the standard 
#  mo-fr will be assumed to count as workdays. If an invalid set of workdays 
#  is given, no workdays will be added.
#
#  Requirements: FMP 18+
# 
#  Author:
#  Menno van Beek
# 
#  20220104 	Initial version
#  
#  ©2022 - Menno van Beek - Van Beek Zakelijke Software - the Netherlands
# 
################################################################## */

While ( [ 
	input = Filter ( workdays ; "01" ) ; 
	thedate = GetAsDate ( startdate ) ; 
	workdays = Case ( 
	 	 Length ( input ) = 7 ; input ; 
	 	 IsEmpty ( input ) ; "0111110" ) ;  
	days = daystoadd ; 
	i = 0 
] ; 
	days > 0 and length ( workdays ) = 7
; [ 
	i = i + 1 ; 
	days = days - GetAsNumber ( 
	 	 	 Middle ( workdays ; 
	 	 	 	 DayOfWeek ( thedate + i ) ; 1 ) )
] ; 
	thedate + i 
)

Add workdays to a date by skipping non-work days. The workdays of a week can be set in the second parameter or left empty, an illegal entry will cause the function not to calculate a new date.

So a few examples:
AddWorkDays ( Date ( 1 ; 4 ; 2022 ) ; "0111110" ; 5 ) = 01/11/2022 /* Workdays: mo-fr */
AddWorkDays ( Date ( 1 ; 4 ; 2022 ) ; "0111100" ; 5 ) = 01/12/2022 /* Workdays: mo-th */
Faulty input:
AddWorkDays ( Date ( 1 ; 4 ; 2022 ) ; "01100" ; 5 ) = 01/04/2022 .... No days are added, because string of workdays is incorrect.

Comments

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

AddWorkDays ( startdate ; workdays ; daystoadd )

Comments

Support this website.

Support this website.