Brian Dunning's FileMaker Custom Functions

WeekdaysOffset ( startDate ; offset )

Returns the specified date plus or minus the specified number of weekdays (Monday to Friday).

  Average rating: 3.9 (29 votes) Log in to vote

Kurt Otto   Kurt Otto
ottopiaDESIGN
http://ottopia.dyndns.org/

Share on Facebook Share on Twitter

  Sample input:
WeekdaysOffset ( "10/4/2005" ; 5 )

WeekdaysOffset ( "10/4/2005" ; -5 )
  Sample output:
11/10/2005

27/9/2005

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

Format
------
WeekdaysOffset ( startDate ; offset )

Parameters
----------
startDate - any date expression or field containing a date.
offset - any numeric expression or field containing a number, representing which number of weekdays you want to add or subtract from startDate.

Data type returned
----------------
date

Description
----------
Returns the specified date plus or minus the specified number of weekdays (Monday to Friday).

Examples
--------
This example assumes that the system date format is DD/MM/YYYY.

WeekdaysOffset ( "10/4/2005" ; 5 ) returns 11/10/2005
WeekdaysOffset ( "10/4/2005" ; -5 ) returns 27/9/2005

 

Comments

Andrew Grant   Andrew Grant, Roseburg, OR
Aug 23, 2010
Great function. I would like to count the current day and am having some trouble figuring how to include it.

If it is Monday, and the offset is 5, I would like the date to show the following Friday rather than the following Monday.

Any suggestions on how the modification would look?
 
Kurt Otto   Kurt Otto, Rosebank
Aug 23, 2010
Have you considered just subtracting a day from your offset? :-)

Otherwise if you are always counting the current day in your offset, just add or subtract a 1 where appropriate:

If (

// Offset is a negative value
offset < 0 ; startDate + Int ( (offset + 1) / 5 ) * 7 - Middle( "23456034560145601256012360123412345" ; ( DayOfWeek ( startDate ) - 1 ) * 5 + Mod ( Abs ( (offset + 1) ) ; 5 ) + 1 ; 1 ) ;

// Offset is a positive value
startDate + Int ( (offset - 1) / 5 ) * 7 + Middle ( "12345012340123601256014560345623456" ; ( DayOfWeek ( startDate ) - 1 ) * 5 + Mod ( (offset - 1) ; 5 ) + 1 ; 1 )

)
 

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.