Often it is useful to return the date of a holiday for a given year, perhaps for a schedule application.  Neither Excel nor VBA have any built in functions for working with holidays -- you have to create your own.   Holidays can be thought of as being either Fixed or Floating.  Fixed holidays are those that occur on the same day each year, such as Christmas.   Floating holidays are those which occur on different days in different years.  For example, Thanksgiving (in the US) occurs on the fourth Thursday of November.  Therefore, we need a function that will calculate the fourth Thursday.  We'll generalize that function, in both VBA and worksheet function form, to return the Nth day-of-week for any month and year.   

Other floating holidays are a bit harder to calculate.  For example, in the US, Memorial Day occurs on the last Monday of May.  Depending on the year, this may be either the 4th or 5th Monday. So we need a function to calculate the number of Mondays in May.  We'll generalize this to compute the number of any day-of-week in any month and year.

Finally, there is Easter, whose actual date is some bizarre result of the phases of the moon. I don't claim credit for the formulas for calculating Easter shown below, nor do I claim to understand why the work, but they do.

Fixed Holidays

For fixed holidays, such as Christmas, this is simple since the date of the holiday does not change from year to year.  For example, use the following to return the date of Christmas in the current year:

=DATE(YEAR(NOW()),12,25)

Floating Holidays

Other holidays, however, are not assigned to a specific date.  For example, Thanksgiving Day is defined to be the 4th Thursday of November.  Therefore its exact date will change from year to year.   For Thanksgiving, we have an explicit VBA function:

We can generalize this to holidays that are defined as the Nth Day of some month, such as Martin Luther King's birthday, celebrated on the 3rd Monday of January.  The following function will return the Nth DayOfWeek for a given month and year:

To return the date of the 3rd Monday in January of 1998, use
=NDow (1998, 1, 3, 2)

The NDow function can also be written as a worksheet formula: 

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)
+(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr,Mon, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

This will work fine for most floating holidays.  However, Memorial Day is celebrated on the Last Monday of May.  To compute this date, we first need a function to tell us how many Mondays there are in the month.

=NDow(1999,5,DowsInMonth(1999,5,2),2)

Easter

Finally, there is Easter, whose date is very complicated to determine from year to year.   I did not write the following formula.  I don't know who did, but it seems to work.

Public Function EasterDate(Yr As Integer) As Date

Dim d As Integer
d = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterDate = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 - ((Yr + Yr \ 4 + _
d + (d > 48) + 1) Mod 7)

End Function

You can also calculate the date of Easter with a worksheet function.  For USA style dates (mm/dd/yyyy), use 

For European style dates (dd/mm/yyyy), use 

=FLOOR(DAY(MINUTE(B2/38)/2+56)&"/5/"&B2,7)-34

Another formula, which does not rely on date formatting was submitted by Joost Verbeek.

Other Date And Time Related Procedures are described on the following pages.

Adding Months And Years

The DATEDIF Function

Date Intervals

Dates And Times

Date And Time Entry

Date And Time Arithmetic

Distributing Dates Over Intervals