VBA Express : Excel - Calculating the Dates of Holidays

Excel

Calculating the Dates of Holidays

Ease of Use

Easy

Version tested with

2002

Submitted by:

Jacob Hilderbrand

Description:

For certain holidays, the actual date of the holiday changes from year to year. These functions allow you to calculate the date of, for example, the second Monday in March 2004. It also allows you to calculate how many of a certain day there are in a month, for example, how many Mondays there are in May of 2004.

Discussion:

I use these functions myselft as part of a calendar program. I wanted a way to calculate certain holidays and add them to the calendar. That is easy for holidays like Christmas that are always on December 25, but many holidays change from year to year.

Code:

instructions for use

			Public Function FloatingHoliday(xYear As Integer, xMonth As Integer, xDay As Integer,  _ 
    xNumber As Integer) 
     
    FloatingHoliday = DateSerial(xYear, xMonth, (8 - Weekday(DateSerial(xYear, xMonth, 1),  _ 
    (xDay + 1) Mod 8)) + ((xNumber - 1) * 7)) 
     
End Function 
 
Public Function TotalDays(xYear As Integer, xMonth As Integer, xDay As Integer) 
     
    Dim x       As Integer 
    Dim EndDate As Integer 
    EndDate = Day(DateSerial(xYear, xMonth + 1, 0)) 
    For x = 1 To EndDate 
        If Weekday(DateSerial(xYear, xMonth, x)) = xDay Then 
            TotalDays = TotalDays + 1 
        End If 
    Next x 
     
End Function

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.

Test the code:

In a blank cell in the workbook, type =floatingholiday(2004,11,5,4) where 2004 is the year, 11 is the month (November), 5 is the Day (Thursday) {Sunday = 1, Monday = 2 etc.}, and 4 is the nth day of that month, in this case we want the 4th Thursday of November 2004 (Thanksgiving).
In a blank cell in the workbook, type =TotalDays(2004,5,2) where 2004 is the year, 5 is the month (May), 2 is the day (Monday) {Sunday = 1, Monday = 2 etc.}, in this case we want to know how many Mondays there are in May 2004.

Sample File:

udfdates.zip 6.75KB

Approved by mdmackillop

This entry has been viewed 386 times.