There are always exceptions to the rule when it comes to calculating Holidays. Aside from Easter, Victoria Day (Canada) and Memorial Day (US) are examples of two holidays that don’t follow the typical rules.

Canadians celebrate Victoria Day on the Monday that occurs on or before May 24. This weekend is commonly referred to as the May “Two-Four” weekend even though the actual holiday doesn’t always occur on the 24th day of May.

Another holiday that doesn’t follow the normal “nth Day of the Week in a month” formula is Memorial Day which occurs on the last Monday of the Month of May. The number of Mondays is a variable that again presents a unique challenge that can be solved with the same logic as the Canadian Victoria Day problem.

With a little VBA, the calculation for these holidays is fairly straightforward. Simply insert the code for the following function in a standard VBA module.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Author: Redge@VergenceAnalytics.com

' OnOrB4Date(Year, Month, Day, DayofWeek)

'Victoria Day, May 24, Would be Calculated as Follows:

'

' **OnOrB4Date(YEAR(Now(), 5 , 24, 2)**

'

' Where, 5 is the Month (May), 24 is the Day of the Month, and

' 2 is the Day of the Week = MONDAY

'

'Memorial Day, Last Monday in May = Monday On or Before 31 May and

'Would be 'Calculated as Follows:

'

' **OnOrB4Date(Year(Now(), 5, 31, 2)**

'

' Where days of the Week are numbered as follows:

' Sun = 1

' Mon = 2

' Tue = 3

' Wed = 4

' Thu = 5

' Fri = 6

' Sat = 7

'

Public Function OnOrB4Date(Yr As Long, Mnth As Long, OnOrB4Day As Long, DayOfWeek As Long)

OnOrB4Date = DateSerial(Yr, Mnth, OnOrB4Day) - ((DateSerial(Yr, Mnth, OnOrB4Day) - DayOfWeek) Mod 7)

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

As you may have already noticed, this function can be used to calculate the Date for any Day of the Week on or before a given Date as demonstrated by the two examples.

**TIP! Finding the Last Day of the Month**

There is a very simple trick to finding the last day of the month. Simply refer to Day 0 of the NEXT month. In other words, May 31 is equivalent to June 0. For our Memorial day example, we could have also used *OnOrB4Date(Year(Now(), 6, 0, 2)* to get the date for the last Monday of May for the current year.

For more indepth information on working with dates and date calculations, we encourage you to visit Chip Pearson’s website for additional details and functions for working with dates. It is not our intent to re-invent the wheel where information is already available, clearly developed, demonstrated, and where excellent examples are provided .

The above code segment was driven by a unique need for our Holiday Calendar that, at the time, did not readily exist.

Feel free to leave a comment or send me an e-mail at Excelytics@gmail.com or Redge@VergenceAnalytics.com