SubscribeHow to Find Dates of Public Holidays using Excel
Lets celebrate these holidays in PHD Style. By learning few excel formulas that you can use to find out dates for some of the popular public holidays like – labor day, memorial day etc.
When is Labor Day (US) in 2010?
Labor day (the US variant) is celebrated on first Monday of every September. It occurs on Sep 6th in 2010.
Using excel date formulas, you can easily find out the labor day’s date for any given year.
Here is the formula I have used:
How this formula works?
The formula finds the weekday of first of September (WEEKDAY(DATE(2010,9,1))) and then uses this information to return one of the possible dates for first Monday.
You can use similar logic to find dates for other holidays like Thanksgiving day (both US and Canada), Memorial Day and Martin Luther King Day.
Is there a long weekend for New Years Day in 2011?
You can use excel to answer questions like whether there will be a long weekend for a given holiday. A long weekend occurs when the holiday is on either Thursday or Monday. So for example, you can check the long weekend condition for January 1st, 2011 like this:
How this formula works?
That is your homework. Go figure!
Download Example Worksheet and Learn by Playing with the Formulas
I have prepared a simple worksheet with 8 examples to calculate the dates for Thanksgiving dates, New years Day of Week, Martin Luther King Day, Memorial Day, Labor Day, and Indian variants of Independence and Republic Days. Go ahead and download the example workbook and play with it.
Related Material:
Help on formulas used in this tutorial: WEEKDAY Formula | IF Formula | OR Formula | 75 Excel Formulas – My eBook.
More examples and tutorials on Excel Date Formulas and Features.
Perpetual Excel Calendar – Free Downloadable Template
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
I would never have thought of using CHOOSE. I’m more a MOD sort of person.
@Chandoo
Did you forget about my post in your Excel “Findout Thanksgiving Day’s Date for Any Year” blog entry on November 25th? Here is the part that applies to the first part of this current blog entry…
Thanksgiving (the fourth Thursday in November)…
=DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3))
The generic version of this formula (which I first saw posted online by Peo Sjoblom, although I don’t know if it was original with him or not), which can be used to find the Nth such and such day of a given month for a given year, looks like this…
=DATE(Year,Month,1+7*NthDay)-WEEKDAY(DATE(Year,Month,8-DayOfWeek))
So, just plug in the year, month, NthDay and DayOfWeek (1 for Sunday, 2 for Monday, etc.) and perform the indicated math. As an example, for this year’s US Thanksgiving…
Year = 2009
Month = 11
DayOfWeek = 5 (for Thursday)
NthDay = 4 (for 4th Thurday in the month)
which gives…
=DATE(2009,11,1+7*4)-WEEKDAY(DATE(2009,11,8-5)
which reduces to the formula I indicated earlier.
I believe you meant Friday instead of Thursday when determining a long weekend since Thursday really doesn’t match the Weekday formula’s value equal to 6, which is Friday.
@Derek… you can use an additional parameter to the weekday function and remove the need for choose altogether. I posted this version of formula as it is more easy to understand. As such date formulas are tricky and complicated ones are more so.
@Rick… No, I remember your beautiful formula very much. I have provided a longer version of the formula as it is easy to understand from a learner’s point of view.
@Gregory… yes, I meant friday.. thanks for pointing it out.