GetDayOccurrence ( TheDate ; Weekday ; Reps )

Returns a date from a selected date for a specific day of the week that is N away.

Average rating: 4.5 (28 votes) Log in to vote

Ken Newell

Sample input:

GetDayOccurrence ("6/1/2004"; "Sat"; "3)

Sample output:

6/19/2004

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

/*
==================================================
  05/30/2004  1.0  KLN  Original Version

 Gives the Nth Occurance from a given date.  Returns "" If Improper weekday is 
 entered or less than 1 reptation (i.e. only calculates forward to a date in the future)
 Format for WeekDay is the same as returned by FM with the DayName() function
 or can be a standard three letter day format.  A date is returned.

     TheDate = Date to calculate from
     Weekday = Name of day to find
     Reps = Number of day cycles in the future to find
==================================================
*/

Let (DayToGet = 
     Case(
          Weekday = "Sunday" or Weekday = "Sun"; 1;
          Weekday = "Monday" or Weekday = "Mon"; 2;
          Weekday = "Tuesday" or Weekday = "Tue"; 3;
          Weekday = "Wednesday" or Weekday = "Wed"; 4;
          Weekday = "Thursday" or Weekday = "Thr"; 5;
          Weekday = "Friday" or Weekday = "Fri"; 6;
          Weekday = "Saturday" or Weekday = "Sat";7;0);

If ( DayToGet=0 or Reps < 1 ; "" ; 
     If (DayToGet = DayOfWeek ( TheDate ); TheDate + (Reps *7);
          If ( DayOfWeek ( TheDate )  < DayToGet; TheDate + (DayToGet -DayOfWeek( TheDate)) + ((Reps-1) *  7) + 1;
                TheDate  - (DayOfWeek ( TheDate ) -  DayOfWeek ( DayToGet ))+ (Reps) *  7) - 1

)))

What is the 3rd Saturday from 6/1/2004? It would be 6/19/2004. This calculation figures those type of questions for you. Handy for identifiying dates that might be bunched together for batch processing. Multiple dates could be pointed to the same end date depending on what day of the week the start date is and what day of the week is selected to match to.

Comments

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

GetDayOccurrence ( TheDate ; Weekday ; Reps )

Comments

Support this website.

Support this website.