Hello,
I'm trying to dynamically calculate the dates that certain holidays
would fall on based on the year. Some of them are easy, because they
are the same date every year. However, there are some that fall on
different dates...i.e. Thanksgiving (in the U.S.) being the 4th
Thursday in November. I'm sure I can write a UDF that would do this,
but I like to avoid doing that when native functions will suffice.
However, I can't seem to figure a way to do this using what's already
there.

So, for instance, if I have 2008 in, say, A1, then the formula to
calculate Thanksgiving should return 11/27/2008. If I change the year
in A1 to 2012, then the formula would return 11/22/2012.

If it's possible, does anyone have any suggestions?

Excel 2003, SP2, Analysis ToolPak
XPPro, SP2

Thanks to all.

Mike Lee
Coppell, TX, USA

have a look here

http://www.cpearson.com/excel/holidays.htm

Mike

"mikelee101" wrote:

Hello,
I'm trying to dynamically calculate the dates that certain holidays
would fall on based on the year. Some of them are easy, because they
are the same date every year. However, there are some that fall on
different dates...i.e. Thanksgiving (in the U.S.) being the 4th
Thursday in November. I'm sure I can write a UDF that would do this,
but I like to avoid doing that when native functions will suffice.
However, I can't seem to figure a way to do this using what's already
there.

So, for instance, if I have 2008 in, say, A1, then the formula to
calculate Thanksgiving should return 11/27/2008. If I change the year
in A1 to 2012, then the formula would return 11/22/2012.

If it's possible, does anyone have any suggestions?

Excel 2003, SP2, Analysis ToolPak
XPPro, SP2

Thanks to all.

Mike Lee
Coppell, TX, USA

You will have to pardon the unreadability of the following function, but it
was written in response to take a longer function and make it into a
one-liner.... I can't find the original function right now, but I did find
this...

Function OrdinalDate(ByVal OrdinalNum As Long, _
ByVal DayNum As Long, _
ByVal MonthNum As Long, _
ByVal YearNum As Long) As Date
OrdinalDate = DateSerial(YearNum, MonthNum, ((7 + DayNum - _
Weekday(DateSerial(YearNum, MonthNum - _
(OrdinalNum = 5), -(OrdinalNum < 5))) + _
15 * (OrdinalNum = 5) + 1) Mod 7) - _
Day(DateSerial(YearNum, MonthNum + 1, 0)) * _
(OrdinalNum = 5)) - 7 * (OrdinalNum - 1) * _
(OrdinalNum < 5)
End Function

You specify which weekday you want in the first argument (4 for 4th Thursday
in November), the weekday number itself in the second argument (5 for the
Thursday... Sunday is 1, Monday is 2, etc... as in 4th Thursday in
November), the month and year numbers in the third and fourth arguments. So,
Thanksgiving for this year would fall on...

OrdinalDate(4, 5, 11, 2008)

One note about the function... it lets you put in 5 for the first argument
(number for the ordinal weekday number)... the 5 means the last specified
weekday in the month. So, to get the date for Memorial Day (in the US) this
year, which is the last Monday in May, you would use this...

OrdinalDate(5, 2, 5, 2008)

I implemented this feature so you could easily get the date when the holiday
would fall on the 5th rather than 4th ordinal weekday of the month, such as
happens for Memorial Day in 2010. If there are not 5 ordinal weekdays in the
month, the function assumes the 4th ordinal weekday is wanted (hence, the 5
for "last" one in the month).

Rick



"mikelee101" wrote in message
...
Hello,
I'm trying to dynamically calculate the dates that certain holidays
would fall on based on the year. Some of them are easy, because they
are the same date every year. However, there are some that fall on
different dates...i.e. Thanksgiving (in the U.S.) being the 4th
Thursday in November. I'm sure I can write a UDF that would do this,
but I like to avoid doing that when native functions will suffice.
However, I can't seem to figure a way to do this using what's already
there.

So, for instance, if I have 2008 in, say, A1, then the formula to
calculate Thanksgiving should return 11/27/2008. If I change the year
in A1 to 2012, then the formula would return 11/22/2012.

If it's possible, does anyone have any suggestions?

Excel 2003, SP2, Analysis ToolPak
XPPro, SP2

Thanks to all.

Mike Lee
Coppell, TX, USA

Mike,
Just sent you a workbook with a list of holidays and calculations on it.
Some calcs from John Walkenbach, some from Chip Pearson and even a couple
that I calculated :O !
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"mikelee101" wrote:

Hello,
I'm trying to dynamically calculate the dates that certain holidays
would fall on based on the year. Some of them are easy, because they
are the same date every year. However, there are some that fall on
different dates...i.e. Thanksgiving (in the U.S.) being the 4th
Thursday in November. I'm sure I can write a UDF that would do this,
but I like to avoid doing that when native functions will suffice.
However, I can't seem to figure a way to do this using what's already
there.

So, for instance, if I have 2008 in, say, A1, then the formula to
calculate Thanksgiving should return 11/27/2008. If I change the year
in A1 to 2012, then the formula would return 11/22/2012.

If it's possible, does anyone have any suggestions?

Excel 2003, SP2, Analysis ToolPak
XPPro, SP2

Thanks to all.

Mike Lee
Coppell, TX, USA