WeekdaysOffset ( startDate ; offset )

Returns the startDate plus or minus the offset (specified weekdays [Monday - Friday])

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

Nick Chapin - Show more from this author

Sample input:

WeekdaysOffset ( 12/15/2005 , 5 )

WeekdaysOffset ( 12/17/2005 , 5 )

WeekdaysOffset ( 12/17/2005 , -3 )

Sample output:

12/22/2005

12/23/2005

12/14/2005

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

/*
        WeekdaysOffset ( startDate; offset )

                returns the specified date plus or minus the offset (specified weekdays [Monday - Friday])

*/

Let ( [
          #day = DayOfWeek (startDate );
          #pastFuture = If (offset < 0; "past"; "future" );
          #theStart = Case ( 
                                    #day = 7 and #pastFuture = "future"; // Saturday
                                    startdate - 1;

                                    #day = 1 and #pastFuture = "future"; // Sunday
                                    startdate - 2;

                                    #day = 1 and #pastFuture = "past"; // Sunday
                                    startdate + 1;

                                    #day = 7 and #pastFuture = "past"; // Saturday
                                    startdate + 2;

                                    startDate
                             )
        ];

         If (#pastFuture = "past";
             #theStart + Int (offset / 5 ) * 7 - Middle ("23456034560145601256012360123412345" ; (DayOfWeek (#theStart ) - 1 ) * 5 + Mod (Abs (offset ); 5 ) + 1; 1 );
             #theStart + Int (offset / 5 ) * 7 + Middle ("12345012340123601256014560345623456" ; (DayOfWeek (#theStart ) - 1 ) * 5 + Mod (offset; 5 ) + 1; 1 )
         )
)

Base on Kurt Otto's function by the same name, this version accounts for the startDate being on a Saturday or Sunday.

Comments

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

WeekdaysOffset ( startDate ; offset )

Comments

Support this website.

Support this website.