Search

# How to Find Dates of Public Holidays using Excel

Share

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:

=DATE(2010,9,CHOOSE(WEEKDAY(DATE(2010,9,1)),2,1,7,6,5,4,3))

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:

=IF(OR(WEEKDAY(DATE(2011,1,1))=6,WEEKDAY(DATE(2011,1,1))=2),”Long weekend”,”No long weekend”)

How this formula works?

That is your homework. Go figure!

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.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]

Ever wanted to automatically format values in thousands, millions or billions in Excel? In this article, let me show you two powerful techniques to do just that.

## Related Tips

Charts and Graphs

Learn Excel

Learn Excel

Learn Excel

Excel Howtos

### How to fix SPILL Error in Excel Tables (3 easy solutions)

Financial Modeling

### 12 Responses to “How to Find Dates of Public Holidays using Excel”

1. derek says:

I would never have thought of using CHOOSE. I'm more a MOD sort of person.

2. Rick Rothstein (MVP - Excel) says:

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

Hey Rick, what is going to be the NthDay if we are looking for like the "last" Monday or the "last" Thursday" etc. I mean, we can put 1 for the "First" and 2 for the "second" etc .. The "Last"??

U rock man!

3. Gregory says:

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.

4. Chandoo says:

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

5. Shajid says:

Dear Sir,

I leave in Bangladesh, when I calculate workdays it calculate with default excel calender. But I need to calculate with my countries calender. How ? Actually I want to set my holidays in the excel Calender. Please help me.

Shajid
shajid73@yaooo.com

• Hui... says:

@Shajid

Excel isn't aware of calendars
Some of its functions have Calendar and Holiday awarness

Workdays
Networkdays
Can have Holidays set

Workdays.intl
Networkdays.intl
Can have Calendars and Holidays set

You should read the help on each function.

• Shajid says:

In our country up 2,00,000 income your tax is 0%
Then next 3,00,000 income your tax is 10%
Then next 4,00,000 income your tax is 15%
then next 3,00,000 income your tax is 20%
then next above your tax is 25%

in the first slab I am entering the formula like
=IF((Total income-200,000)<300000,(Total income-200000)*10%,300000*10%)

Problem is when (Total income - 2,00,000 = Negative figure then it is calculating negative tax, which I don not want.

Shajid

• RK says:

Use this formula:

IF(Total Income>1200000,(150000+(Total Income-1200000)*25%),IF(Total Income>900000,(90000+(Total Income-900000)*20%),IF(Total Income>500000,(30000+(Total Income-500000)*15%),IF(Total Income>200000,((Total Income-200000)*10%),0))))

6. Shajid says:

Yes I solved my problem myself.

7. Thang says:

Hi Pro, i want to know what day if given date and week, for example : if given 32th week, Tuesday, 2015 >>> I have to calculate and result is 4. Pls help me!

8. Jon says:

Hello

How to detect if day is bank holiday in one or many given countries ? Poland, India, USA ....

I tried to use international code w. WORKDAYS.INTL, didn't work fine.

Thx
Jon

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.