NoWeekend ( theDate, Avoidance )

Adjust a future date so as not to fall on a weekend

Average rating: 4.3 (34 votes) Log in to vote

Matt Wills - Show more from this author
Virtual Vermont
http://www.virtualvermont.com/FMP/

Sample input:

NoWeekend (Any Sat or Sun Date, Type of Avoidance)

Sample output:

Previous Friday or following Monday Date

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

/* NoWeekend (theDate, Avoidance) */

/*Set Avoidance to "C" to return the closest weekday. Any other value returns the following Monday.*/

Case (

Avoidance = "C" ; //Returns closest Weekday (Friday or Monday)

         theDate + Choose ( DayOfWeek ( TheDate ); 0 ; 1 ; 0 ; 0 ; 0 ; 0 ; 0 ; -1);

         theDate + Choose ( DayOfWeek ( TheDate ); 0 ; 1 ; 0 ; 0 ; 0 ; 0 ; 0 ; 2)
)

Automatically setting a future date (such as for a sales followup in N days or on the first of a month) may return a date which falls on a weekend.

NoWeekend assures that doesn't happen.

Set Avoidance to "C" to return the closest weekday (if the target date falls on Saturday, the previous Friday is returned; if the target date falls on Sunday, the following Monday is returned).

If Avoidance is any other value, the following Monday will be returned.

This CF can be quite useful in conjunction with my Holidays solution, which projects the dates of specified holidays (Free).

Comments

		Roland, London Nov 25, 2010
How do I make it so a Friday is returned instead of a Monday? Hope you can help.

		Roland, London Jan 22, 2011
Well I can answer my own question, now that I understand the function better. To get the Friday before the weekend, just change the second line to: theDate + Choose ( DayOfWeek ( TheDate ); 0 ; -2 ; 0 ; 0 ; 0 ; 0 ; 0 ; -1)

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

NoWeekend ( theDate, Avoidance )

Comments

Support this website.

Support this website.