DateByDayOccur ( yearNum ; monthNum ; dayOfWeekNum ; dayOccurance )

Returns the date of a specific occurrence of a day of the week, for a given month and year. Ideal for holiday calculations.

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

Jonathan Mickelson - Show more from this author

Sample input:

DateByDayOccur ( 2004; 1; 2; 3)

DateByDayOccur ( 2020; 2; 1; "")

Sample output:

1/19/2004
(Which was Martin Luther King Day in 2004)
2/23/2020
(last Sunday in Feb, 2020)

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

Let ( 
[ 
yr = yearNum ;
mon = monthNum ; 
weekNum = dayOccurance ; 
dayOWeek = dayOfWeekNum - 1 ;                                                           // Adjust FileMaker Day of Week to a 0-6 system
lastday = Day ( Date ( mon + 1 ; 1 ; yr ) - 1 ) ;                                         // Is total number of days in month
weekTmp =  If ( weekNum < 1 ; 
                         ( DayOfWeek ( Date ( mon ; lastday ; yr ) ) - 1 ) - Mod ( dayOWeek ; 7 ) ; 
                         dayOWeek - ( Mod ( DayOfWeek ( Date ( mon ; 1 ; yr ) - 1 ) ; 7 ) )             
                        ) ;
temp = If ( weekTmp < 0 ; weekTmp + 7 ; weekTmp ) ;
dayNum = If ( weekNum < 1 ; lastday - temp ; ( 7 * weekNum ) - 6 + temp ) ; 
result = Date ( mon ; dayNum ; yr )

 ] ; 

  If ( 
      ( weekNum > 5 ) or                                                                          // week must be less than 5
      ( weekNum < 1 and not IsEmpty ( weekNum ) ) or                            // week must be between greater than 1, but Null is OK
      ( dayOWeek > 6 ) or ( dayOWeek < 0 ) ;                                            // ( day_of_week - 1) must be between 0 and 6 (Sun=0...Sat=6)
       "?";                                                                                                  // Error on Bad Input
       result                                                                                              // Final Result

      )    // END IF

)  // END LET

Returns the date of a specific occurrence of a day of the week, for a given month and year. Ideal for holiday calculations. For example, to find the 3rd Tuesday in March of 2008, or the date of the last Friday in December 1954.

Data type returned - Date

Parameters:

yearNum - any valid Year (YYYY format)
monthNum - a Number 1-12 representing a calendar month.
dayOfWeekNum - a number 1-7 (Where Sunday = 1 ... Saturday = 7)
dayOccurance - a number 1-5 For the desired occurrence of the day of the week. Null or empty values locate the LAST occurrence of the day of the week.
For example, to find the last Saturday in May 2001 you would use: DateByDayOccur ( 2001; 5; 7; "" )

Acknowledgements - This custom function draws upon work from:
- Dan Kaplan, abledesign.com, for a similar PHP calculation.
- Marcos J. Montes, www.smart.net/~mmontes/ushols.html

Comments

		Alistair Hay, York, U.K. Mar 31, 2016
I want to thank you for this function. You have saved my an unimaginable amount of time re-inventing the wheel! I was given the task of beginning a calendar display on the first Monday of the Month and ending on the last Sunday. Your function was the final key to success. So again, many many thanks Alistair

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

DateByDayOccur ( yearNum ; monthNum ; dayOfWeekNum ; dayOccurance )

Comments

Support this website.

Support this website.