Excel VBA – On or Before Date Calculations

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


About excelytics

Webmaster and Founder of Excelytics. Established 22-Jun-2010.
This entry was posted in Uncategorized and tagged , , , , , , , , . Bookmark the permalink.

2 Responses to Excel VBA – On or Before Date Calculations

  1. Wonderful site and theme, would really like to see a bit more content though!
    Great post all around, added your XML feed! Love this theme, too!

    • excelytics says:

      The content for this site, like many others, will surely build over time. It is not our intent to become an exclusive VBA content provider. Rather, it is our intent to complement other existing sites with some of the puzzle pieces that may still be missing.

      Enjoy and visit often. Our Excel Resources page is coming soon and will provide links to sites we have come to appreciate over the years.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s