×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

Microsoft: Office FAQ

Date/Time Functions

How to calculate dates for holidays by BobJacksonNCI
Posted: 11 Feb 12 (Edited 12 Feb 12)

I wanted to automate finding the dates of holidays and came across
http://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 instances
of 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))+1

Black Friday            Friday after fourth Thursday (Thanksgiving)
=DATE((A1),11,CHOOSE(WEEKDAY(DATE((A1),11,1)),26,25,24,23,22,28,27))+1

Obviously, 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.aspx

UPDATE - 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.  graduate

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

My Archive

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close