Figuring out if a day is a legal holiday

[Bruce]

I need to check to see if a date is a legal holiday or a Sunday. I know
about Workday(), with the holiday feature, but that also takes saturday into
account, and I need to pass a list of the holidays. Testing to see if Sunday
is easy, but it is the holidays that will cause me greif....

I will be checking for various years, so passingg a list of holidays is not
viable.

Is there a calculation to figure out the 10 legal holidays listed below:

New Years Day

Martin Luther King Day

Presidents Day

Memorial Day

Independence Day

Labor Day

Columbus Day

Veterans Day

Thanksgiving Day

Christmas Day



(I don't need Easter, as it is always on Sunday anyways)



Ideally, I would like to pass a date to it, and it would respond with the
legal holiday name, or return an empty string





Thanks!

Bruce

 

[Jim Cone]

Bruce,
I don't believe there is anything built into Excel that defines
holidays. If you can come up with a definition of how
each holiday is determined then you can probably work
out the answer from the definition.
I believe you already know how to determine New Years day,
Independence day and Christmas. <g>
Here is one way to determine Easter, too bad you
don't need that one. (it is probably the most difficult to determine)...

To calculate the date for Easter Sunday for any year between 1900 to 2078.
by Norbert Hetterich from Germany: (year in A1)
=FLOOR(DAY(MINUTE(A1/38)/2+56)&"/5/"&A1,7)-34
This worked for me - JBC
=FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Bruce" <oleexpres.at.johnsonclan.net>
wrote in message
I need to check to see if a date is a legal holiday or a Sunday. I know
about Workday(), with the holiday feature, but that also takes saturday into
account, and I need to pass a list of the holidays. Testing to see if Sunday
is easy, but it is the holidays that will cause me greif....
I will be checking for various years, so passingg a list of holidays is not
viable.
Is there a calculation to figure out the 10 legal holidays listed below:
New Years Day

Martin Luther King Day

Presidents Day

Memorial Day

Independence Day

Labor Day

Columbus Day

Veterans Day

Thanksgiving Day

Christmas Day
(I don't need Easter, as it is always on Sunday anyways)
Ideally, I would like to pass a date to it, and it would respond with the
legal holiday name, or return an empty string
Thanks!
Bruce

 

[NickHK]

Bruce,
There was (is ?) a worldwide holiday update file spanning several years for
Outlook. It's a text file split on countries, so you could easily read it
with VBA Open file statement.
It does contain errors but I would imagine the US holidays are relatively
correct.

If you have Outlook installed, you should have something already. It is
called xxx.hol.
<Sample>
Labor Day,2005/9/5
Lincoln's Birthday,2001/2/12
Lincoln's Birthday,2002/2/12
Lincoln's Birthday,2003/2/12
Lincoln's Birthday,2004/2/12
Lincoln's Birthday,2005/2/12
Martin Luther King Day,2001/1/15
Martin Luther King Day,2002/1/21
Martin Luther King Day,2003/1/20
Martin Luther King Day,2004/1/19
Martin Luther King Day,2005/1/17
Memorial Day,2001/5/28
</Sample>

NickHK