Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Figuring out if a day is a legal holiday
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Figuring out if a day is a legal holiday
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Figuring out if a day is a legal holiday
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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Legal Disclaimer | Excel Discussion (Misc queries) | |||
Printing Legal | Excel Discussion (Misc queries) | |||
Is this legal? | Excel Worksheet Functions | |||
how do I add words, holiday =1, holiday am=0.5 | Excel Worksheet Functions | |||
Holiday Planner show holiday taken? | Excel Worksheet Functions |