Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

How to Find Dates of Public Holidays using Excel

Posted on December 24th, 2009 in Learn Excel - 10 comments

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.

How to Find Dates of Public Holidays using Excel

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!

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

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

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

    • Addis says:

      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:

        Thanks for your valuable suggestion. Now I am facing another problem when I calculating my TAX spreadsheet. If possible please help me.
        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.

        Please help me.

        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.

Leave a Reply