HolidayNameByDate ( myDate )

Returns the Holiday Name matching a Date, i.e. for a calendar. A dynamic holiday list is built within for stand-alone operation.

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

Jonathan Mickelson - Show more from this author

Sample input:

HolidayNameByDate ( getAsDate ( "1/1/2004" ) )
HolidayNameByDate ( getAsDate ( "12/25/2010" ) )
HolidayNameByDate ( getAsDate ( "2/25/2010" ) )

Sample output:

"New Year's Day"
"Christmas"
(blank result)

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

Let ( 
 [ 
myDate =  theDate ; 
Y = Year ( myDate ) ; 
// ------------- BEGIN EASTER CALCULATION ---------------
// --------- Begin OUDIN's Easter (Western) Algorithm ----------
century = Div ( Y ; 100 ) ; 
G = Mod ( Y ; 19 ) ; 
K = Div ( century - 17 ; 25 ) ; 
I = Mod ( ( century - Div ( century ; 4 ) - Div ( century - K ; 3 ) + 19 * G + 15 ) ; 30 ) ; 
Ia = I - Div ( I ; 28 ) * ( 1 - Div ( I ; 28 ) * Div ( 29 ; I + 1 ) * Div ( 21 - G ; 11 ) ) ; 
J = Mod ( Y + Div ( Y ; 4 ) + Ia + 2 - century + Div ( century ; 4 ) ; 7 ) ; 
L = Ia - J ; 
EasterM = 3 + Div ( L + 40 ; 44 ) ; 
EasterD = L + 28 - 31* Div ( EasterM ; 4) ; 
// --------- Begin OUDIN's Easter (Western) Algorithm ----------

/*
THIS OPTIONAL EASTER FUNCTION IS PROVIDED FOR AN ALTERNATIVE
BUT THEY BOTH RESULT IN THE SAME RESULTS!! 
// --------- Begin BUTCHER's Easter (Western) Algorithm ----------
a = Mod ( Y ; 19 ) ; 
b = Div ( Y ; 100 ) ; 
c = Mod ( Y ; 100 ) ; 
d = Div ( b; 4 ) ; 
e = Mod ( b ; 4 ) ; 
f = Div ( b + 8 ; 25 ) ; 
g = Div ( b - f + 1 ; 3 ) ; 
h = Mod ( 19 * a + b - d - g + 15 ; 30 ); 
i = Div ( c ; 4 ) ; 
k = Mod ( c ; 4 ) ; 
l = Mod ( 32 + 2 * e + 2 * i - h - k ; 7 ); 
m = Div ( a + 11 * h + 22 * l ; 451 )  ; 
EasterM = Div ( h + l - 7 * m + 114 ; 31 ) ;         // 3=March, 4=April 
EasterD = Mod ( h + l - 7 * m + 114 ; 31 ) +1 ;  // day in Easter Month 
// --------- Begin Butcher's Easter (Western) Algorithm ----------
*/

// EASTER RESULT
easterDate = Date ( EasterM ; EasterD ; Y ) ; 
// ------------- END EASTER CALCULATIONS ---------------

// ---------------DEFINE HOLIDAYS LIST -------------------
// DateByDayOccur( yearNum; monthNum; dayOfWeekNum; dayOccurance)
//   - dayOfWeekNum:  1=Sun,2=Mon,3=Tue,4=Wed,5=Thu,6=Fri,7=Sat 
//   - dayOccurance:   1=1st,2=2nd,3=3rd,4=4th,5=5th,""=Last
// Builds a return delimited list of Dates and Titles separated by a "=" 

holidayList = 
// NEW YEARS - Jan 1
      "New Year's Day = " & 
      Date ( 1 ; 1 ; Y ) & "¶" 
& 
// NEW YEARS (OBSERVED) - If it falls on a Sunday, it's observed Monday
Case ( 
  DayOfWeek ( Date ( 1 ; 1 ; Y ) ) = 1 ; 
     "New Year's Day (Observed) = " & 
      Date ( 1 ; 1 ; Y ) + 1 & "¶" 
) // END Case
& 
// MARTIN LUTHER KING - *Third Monday in January*
      "Martin Luther King Day = " &
       DateByDayOccur ( Y ; 1 ; 2 ; 3 ) & "¶" 
&
// VALENTINE'S DAY - Feb 14
     "Valentine's Day = "  & 
     Date ( 2 ; 14 ; Y )& "¶" 
& 
// PRESIDENT'S DAY - *Third Monday in February*
     "President's Day = " & 
     DateByDayOccur ( Y ; 2 ; 2 ; 3 ) & "¶" 
& 
// ST. PATRICK'S DAY - Mar 17
     "St. Patrick's Day = " & 
     Date ( 3 ; 17 ; Y ) & "¶" 
& 
// EASTER - See Easter Calc Above 
     "Easter = "& 
      easterDate & "¶" 
& 
// CINCO DE MAYO - May 5 
     "Cinco De Mayo = " & 
     Date ( 5 ; 5 ; Y ) & "¶" 
& 
// MEMORIAL DAY - *Last Monday in May* 
     "Memorial Day (Observed) = " & 
      DateByDayOccur ( Y ; 5 ; 2 ; "" ) & "¶" 
& 
// INDEPENDANCE DAY - July 4 
     "Independence Day = " & 
      Date ( 7 ; 4 ; Y )& "¶" 
& 
// INDEPENDENCE DAY - If a weekend, Choose weekday 
Case ( 
  DayOfWeek ( Date ( 7 ; 4 ; Y ) ) = 7 ; 
     "Independence Day (Observed) = " & 
      Date ( 7 ; 4 ; Y ) - 1 & "¶" ; 

  DayOfWeek ( Date ( 7 ; 4 ; Y ) ) = 1 ; 
     "Independence Day (Observed) = " & 
     Date ( 7 ; 4 ; Y ) + 1 & "¶" 
)  // END Case
&
// LABOR DAY- *First Monday in September* 
     "Labor Day = " & 
      DateByDayOccur ( Y ; 9 ; 2 ; 1 ) & "¶" 
& 
// COLUMBUS DAY - *Second Monday in October* 
     "Columbus Day (Observed) = " & 
      DateByDayOccur ( Y ; 10 ; 2 ; 2 ) & "¶" 
& 
// HALLOWEEN - Oct 31 
     "Halloween = " & 
      Date ( 10 ; 31 ; Y ) & "¶" 
& 
// THANKSGIVING DAY - *Fourth Thursday in November* 
     "Thanksgiving Day= " & 
      DateByDayOccur ( Y ; 11 ; 5 ; 4 ) & "¶" 
& 
// CHRISTMAS DAY - Dec 25 
     "Christmas Day = " & 
     Date ( 12 ; 25; Y ) & "¶" 
& 
// NEW YEARS NEXT YEAR if it falls on Saturday it moves back a day and a year!
Case ( 
  DayOfWeek ( Date ( 1 ; 1 ; Y + 1 ) ) = 7 ; 
    "New Year's Day " & Y + 1 & " (Observed) = " & 
     Date ( 1 ; 1 ; Y + 1 ) - 1 & "¶" 
)   // END Case
; // END holidayList
// ------------ BEGIN PROCESS VARIABLES -----------------
searchCrit     = GetAsDate ( myDate ) ;
exists            = PatternCount ( holidayList ; "= " & searchCrit ) ; 
whereEnd      = Position ( holidayList ; searchCrit ; 0 ; 1 ) + Length ( searchCrit ) ; 
count            = PatternCount ( Left ( holidayList ; whereEnd ) ; "¶" )  ; 
value             = MiddleValues ( holidayList ; count ; 1 ) ; 
holNameRaw = Left ( value ; Position ( value ; "=" ; 0 ; 1 ) - 1 ) ; 
holidayName = Trim ( holNameRaw ) 
] ; 
// ----------- CUSTOM FUNCTION RESULTS ---------------- 
Case (
         // -- ERROR: Return "Null" Result if any test  fails!
         not IsEmpty ( myDate ) and                  //-The date was full
         IsValid ( GetAsDate ( myDate ) ) and     //-The date is valid
         exists > 0 ;                                          //-Date found in Holiday List

         // -- SUCCESS! Display Result
         holidayName
        )  // END CASE
)  // END LET

UPDATED: 4/6/2010
CHANGE: Added "= " literal text in front of the "exists" variable's patterncount test to correct for matching of partial dates in list, which incorrectly flagged 2/25/2010 as "Christmas", in addition to the correct 12/25/2010. Thanks to Bill Doerrfeld, for pointing out this logic problem!

WARNING COMPLEX CALCULATIONS WITHIN!

This function returns the corresponding US (can be modified for other countries) Holiday Name for the supplied Date. The list of Holidays can be found/modified/expanded in the Variable Declarations within the calculation.

The Holiday List used internally is identical to the list generated by the another separate custom fucntion which can also be used to display this list, called "HolidayListByYear ( yearNumber )" and could be substituted for the holidayList variable within the calc, if one wanted to make it dependant upon an external Custom Function. The Author opted for a stand-alone approach.

The result is null ("") if there is no match, so that it can be used as an autoenter in a calendar solution or other type of value lookup.

NOTE: This function REQUIRES the use of another custom function called: "DateByDayOccur" which performs date calculations for dates which occur on certain occurances of a day within a month, as in the example of Thanksgiving which falls on the "Fourth Thursday of November".

Output: Text - Holiday Name

Parameters: myDate - Any valid Date

Special thanks: To Dan Kaplan, of abledesign.com for distilling
many holiday/calendaring sources on the net.

Comments

		Bill Doerrfeld, Seattle Apr 5, 2010
The following line: exists = PatternCount ( holidayList ; searchCrit ) ; leads to failure as 12/25/2010 and 2/25/2010 both contain the "2/25" pattern. Accordingly, you'll get Xmas day on Feb. 25th. Xmas twice a year, nice!

		Jonathan Mickelson Apr 6, 2010
Good catch Bill, we'd been looking into this recently as we got a couple of false hits like Thanksgiving in January! I'll be looking into updating the calc soon! If anyone beats me to it post as a comment and I'll make the update! best, JM

		Jonathan Mickelson Apr 6, 2010
The change has been made and updated on the site, thanks again Bill, for pointing this out! The line: exists = PatternCount ( holidayList ; searchCrit ) ; Was changed to: exists = PatternCount ( holidayList ; "= " & searchCrit ) ;

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

HolidayNameByDate ( myDate )

Comments

Support this website.

Support this website.