×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Microsoft: Office FAQ

## Date/Time Functions

 How to calculate dates for holidays by BobJacksonNCI faq68-7549 Posted: 11 Feb 12 (Edited 12 Feb 12) I wanted to automate finding the dates of holidays and came acrosshttp://chandoo.org/wp/2009/12/24/public-holidays-excel-dates/*** Giving credit where credit is clearly due! ***The following is an alternative to nested IF statements which are harder to understand and explain.  Purists may not like this solution because it includes the equivalent of hard-coded tables as parameters to the CHOOSE instruction.But, it is somewhat easier to understand and explain, and is consistent.Not included are holidays with fixed dates.  E.G., Independence Day - July 4th, Veteran's Day - November 11th, Christmas, etc.Each instruction begins with the first of the month containing the holiday desired.  It then uses the day of the week of the first to make a choice (CHOOSE) from the list following.  I.E., if the first of the month is Wednesday, CHOOSE picks the 4th entry from the following list because Wednesday is the fourth day of the week.  (Sunday being the first day of the week is standard - adjustments are required if your setting changes the default from Sunday being the first day of the week.)The pattern is obvious, but not so easy to explain.  To prove accuracy, enough years must be tested for each instruction so all days of a week are included.Instructions assume the year of interest is loaded into cell A1.If you want to use the current year per your PC, replace all instancesof DATE((A1) with DATE(YEAR(TODAY())FEDERAL HOLIDAYS:Martin Luther King      Third Monday in January=DATE((A1),1,CHOOSE(WEEKDAY(DATE((A1),1,1)),16,15,21,20,19,18,17))President's Day         Third Monday in February=DATE((A1),2,CHOOSE(WEEKDAY(DATE((A1),2,1)),16,15,21,20,19,18,17))Memorial Day            Last Monday in May=DATE((A1),5,CHOOSE(WEEKDAY(DATE((A1),5,1)),30,29,28,27,26,25,31))Labor Day                First Monday in September=DATE((A1),9,CHOOSE(WEEKDAY(DATE((A1),9,1)),2,1,7,6,5,4,3))Columbus Day            Second Monday in October=DATE((A1),10,CHOOSE(WEEKDAY(DATE((A1),10,1)),9,8,14,13,12,11,10))Thanksgiving             Fourth Thursday in November=DATE((A1),11,CHOOSE(WEEKDAY(DATE((A1),11,1)),26,25,24,23,22,28,27))NON FEDERAL HOLIDAYS:Arbor Day                Last Friday in April=DATE((A1),4,CHOOSE(WEEKDAY(DATE((A1),4,1)),27,26,25,24,30,29,28))Mother's Day            Second Sunday in May=DATE((A1),5,CHOOSE(WEEKDAY(DATE((A1),5,1)),8,14,13,12,11,10,9))Father's Day            Third Sunday in June=DATE((A1),6,CHOOSE(WEEKDAY(DATE((A1),6,1)),15,21,20,19,18,17,16))Election Day            Tuesday after first Monday=DATE((A1),11,CHOOSE(WEEKDAY(DATE((A1),11,1)),2,1,7,6,5,4,3))+1Black Friday            Friday after fourth Thursday (Thanksgiving)=DATE((A1),11,CHOOSE(WEEKDAY(DATE((A1),11,1)),26,25,24,23,22,28,27))+1Obviously, this technique doesn't work for Easter and all holidays that are linked to Easter.  (Easter is governed by ecclesiastical full moon, which isn't covered by Excel functions.)  However, it can be calculated using Visual Basic code.  See:  http://www.cpearson.com/Excel/Easter.aspxUPDATE - Unproven formulas to calculate Easter listed in John Walkenbach's Excel 2010 Bible.  John wrote "I have no idea how they work."Homework assignment - figure out if and how the following calculate Easter dates.  Enter a year only in cell A1=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6=FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34 Back to Microsoft: Office FAQ Index Back to Microsoft: Office Forum

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!