home link

 

 

Synopsis

Some holidays occur on the same day every year, like New Year’s Day on January 1st or Independence Day in the USA on July 4th. But often holidays occur on a specific weekday in a month like May Day in the UK which is on the first Monday of May or Washington’s Birthday on the third Monday in February.


This article will teach you how to calculate holidays that occur on the Nth Monday, or Friday, or Sunday, etc. of the month.

Calculating Variable Holiday Dates

All formulas shown here will work in any version of Excel from Excel 2003 onwards (and most should work in earlier versions too). The downloadable workbook contains examples of the formulas discussed and also lists of all US and UK holidays from 2000 to 2099, calculated using these formulas.

† See the bottom of the article for a link.

Thanksgiving is on the fourth Thursday of November (in the US), which means that the date changes every year.

How would we calculate that date in Excel if we have the year only? Well, with the year in cell C2 this formula will give you the correct date:

=DATE(C2,11,29)-WEEKDAY(DATE(C2,11,3))

…or we can use this shorter version

=FLOOR(C2&"-11-30",7)-2

This article will explain how those formulas work (and why you might not want to use the second one) and show you how to calculate any variable holiday date.

First Principles

First we need to go back to basics …

Suppose I have a date in cell A2, any date, and I want to find the previous Saturday. Well, if you’ve ever used WEEKDAY() function you’ll know that this formula…

=WEEKDAY(A2,1)

…will return the number of the day of the week, beginning on Sunday, so if A2 is a Sunday that formula returns 1, if A2 is a Monday it returns 2 … etc. up to when A2 is a Saturday and it returns 7.

So for our task to find the previous Saturday we could just subtract the weekday number from the date, e.g.

=A2-WEEKDAY(A2,1)

screenshot 1 calendar screenshot

As you can see from the screenshot, if A2 is a Thursday that formula subtracts 5 from A2, giving the previous Saturday… and if it’s a Monday that subtracts 2 from A2, giving the same Saturday, in fact whatever date you have in A2 that formula will give you a Saturday.  If A2 itself is a Saturday then the formula returns the previous Saturday (see row #4 in the left screenshow above).

But what if you don’t want the previous Saturday; you want the previous Monday… or previous Thursday? Well, we can adjust that easily enough. When we wanted Saturday we needed a function that started its counting directly after the day sought, in other words we need a function that returns:

  • a 1 on a Sunday,
  • a 2 on a Monday,
  • a 7 on a Saturday

Therefore, if we seek a Monday, we want a function that starts its count on Tuesday. We want a formula that will return:

  • a 1 on a Tuesday,
  • a 2 on a Wednesday,
  • a 7 on a Monday

So let’s look at WEEKDAY() function more closely. In  =WEEKDAY(A2,1)  the "1" stipulates the start date of the week… and in Excel 2010 or later versions we have several other options.

From Excel 2010 Help
Return_type Number returned
1 or omitted Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2 Numbers 1 (Monday) through 7 (Sunday).
3 Numbers 0 (Monday) through 6 (Sunday).
11 Numbers 1 (Monday) through 7 (Sunday).
12 Numbers 1 (Tuesday) through 7 (Monday).
13 Numbers 1 (Wednesday) through 7 (Tuesday).
14 Numbers 1 (Thursday) through 7 (Wednesday).
15 Numbers 1 (Friday) through 7 (Thursday).
16 Numbers 1 (Saturday) through 7 (Friday).
17 Numbers 1 (Sunday) through 7 (Saturday).

Options 1, 2 and 3 are the same in previous version of Excel but options 11 to 17 are new in Excel 2010.

So if we want to return the previous Wednesday we can pick the version where Thursday = 1, i.e.

=A2-WEEKDAY(A2,14)

…but obviously that will only work in Excel 2010 or later versions - what about something that will work in any Excel version? If we want to return:

  • a 1 on a Thursday,
  • a 2 on a Friday,
  • a 7 on a Wednesday
then what about “offsetting” our date (within WEEKDAY function) by the required number of days, e.g.

A2-WEEKDAY(A2+3,1)

By adding 3 to the date we get the required number — if A2 is a Thursday then A2+3 is a Sunday and so the WEEKDAY() function (with 1 as second argument) will return a 1.

This approach can be adjusted for any day of the week… and it works in earlier excel versions because we are using the default WEEKDAY option, in fact we can omit the 1 as that is the default option, i.e.

=A2-WEEKDAY(A2+3)

Note that  =A2-WEEKDAY(A2+3)  will give you the same result as  =A2-WEEKDAY(A2-4)  but I prefer to always use addition here, that will avoid errors in extreme cases, e.g. when A2 is a date like January 2nd, 1900

screenshot #2 (error)

We can now change the 3 to any number depending on the day we want ( 1 = Fri, 2 = Thu, 3 =Wed, 4 = Tue, 5 = Mon, 6 = Sun, 7 = Sat ).

Calculating Holidays

So now we can get the previous day for any day of the week… but how does that help with the holiday problem? Well, once we have established the concepts above we can extend those to help us…

For example we know we can get the previous Thursday to any date by using:

=A2-WEEKDAY(A2+2)

…so if we want the first Thursday in November 2013 we can substitute A2 in that formula for the 8th of November, 2013 (because the Thursday before the 8th must be the first Thursday in the month) so the formula becomes:

=DATE(2013,11,8)-WEEKDAY(DATE(2013,11,8)+2)

And we can simplify that further by adding the 8 and the 2 at the end:

=DATE(2013,11,8)-WEEKDAY(DATE(2013,11,10))

…or because the weekday of the 10th of November will clearly be the same weekday as a week earlier we can use 3rd rather than 10th, i.e.

=DATE(2013,11,8)-WEEKDAY(DATE(2013,11,3))

…and now we know that U.S. Thanksgiving is 3 weeks after that, so week can just add 21 to the 8 to get 29, i.e.

=DATE(2013,11,29)-WEEKDAY(DATE(2013,11,3))

And generically we can therefore get the Thanksgiving date for any year, assuming the year only in C2

=DATE(C2,11,29)-WEEKDAY(DATE(C2,11,3))

screenshot #3, thanksgivings 2010-2014

And from that we can extrapolate a generic formula to give us any nth x-day of a given month (and year), i.e.

=DATE(yr,mon,1+7*n)-WEEKDAY(DATE(yr,mon,8-x))

Where x is a number which indicates the day of the week to find ( 1=Sun through to 7=Sat ), so in the UK the first Monday in May is May Day “bank holiday” [i.e. a national holiday], so if we use that version and fill in the specifics we get this formula for 2014 May Day

=DATE(2014,5,1+7*1)-WEEKDAY(DATE(2014,5,8-2))

Which simplifies further to this version:

=DATE(2014,5,8)-WEEKDAY(DATE(2014,5,6))

…but what if a holiday is the last instance of a day in a month, e.g. in the UK “Spring Bank Holiday” is the last Monday in May (the same date as “Memorial Day” in the US). How do we find that given that we don’t know whether the last Monday will be the fourth or fifth? The easy way is to take the first Monday in June and subtract 7, i.e. for 2014 again

=DATE(2014,6,1)-WEEKDAY(DATE(2014,6,6))

[or going back to our previous discussion you might want to think about that as just finding the Monday previous to the 1st of June, which, by definition, must be the last Monday in May…]

Easter

You can use the formulas above to find almost all “movable” holidays, but Easter is trickier. Some Easter Sunday formulas are listed here. I like this one (which doesn’t seem to attributed to anybody):

=ROUND(DATE(C2,4,1)/7+MOD(19*MOD(C2,19)-7,30)*14%,0)*7-6

It works for all years up to 2203 and isn’t “region specific” like some of the others. It does have one drawback, though, which is quite interesting. Because it’s using mathematical functions on the date serial number it’s susceptible to error if you use the alternative (1904) date system which is available in Excel – in fact it will return a Saturday in that case.

Alternatives

My suggested formulas above will work with either date system because they use WEEKDAY() function rather than ROUND()/MOD() (because you get the same result when WEEKDAY() is applied to a date, regardless of date system)… but if you prefer shorter formulas you can use FLOOR() function to calculate holiday dates, e.g. for U.S. Thanksgiving (assuming you are using 1900 date system) you can use this formula:

=FLOOR(C2&"-11-30",7)-2

screenshot #4 - thankgivings using FLOOR function

Why does this work? Dates are simply formatted numbers in Excel so you can use mathematical functions on them like any other number. Because 1st Jan 1900 is the equivalent of 1 and 1st Jan 1900 was deemed to be a Sunday (incorrectly, see here) then “day zero” in Excel is a Saturday, so if B2 contains a date this formula will give you the previous Saturday

=FLOOR(B2,7)

[unlike our previous examples, if B2 is a Saturday then the formula returns that exact date].

…and again we can adjust this for other days, so if I use:

=FLOOR(B2,7)-2

I’ll get the previous Thursday… except if B2 is a Friday that will give the day 8 days before not 1 day before, so we need to have an opposite adjustment for the date, i.e.

=FLOOR(B2+2,7)-2

And for Thanksgiving the latest date of that holiday is 28th so we add 2 days to that and get our version:

=FLOOR(C2&"-11-30",7)-2

Note that because C2 contains the year that gives us the date in "yyyy-mm-dd" format which should be a universally recognised date format, even across different regions…

…but as stated previously, this only works with 1900 date system – if you have 1904 date system set that formula will give you an incorrect date (a Wednesday)

Other Holidays

Now we have covered the movable holidays so all that’s left is those that don’t move… although it’s not always as simple as that!

Let’s look at New Year’s Day. This is always on 1st January so if we have the year in C2 as before we can calculate that easily with this formula:

=DATE(C2,1,1)

…but although the official holiday may fall on the 1st, what if that day is a Saturday or Sunday? In most countries workers will be given another day off, so if we are trying to find holidays meaning the actual days not worked then we need to amend that.

In the UK we will always take the following working day as the New Year’s Day holiday, so if the 1st January is a Saturday the next working day is Monday 3rd and that is the holiday… or if January 1st is a Sunday the holiday is on Monday 2nd.

In the US the holiday will normally be taken on the closest working day, so if January 1st is a Sunday then Monday 2nd is the day off work as per the UK… but if the 1st is a Saturday the work holiday is given on the previous day, Friday 31st December.

So for the UK we can simply take the first working day after the 31st December and we’ll get the correct day, i.e.

=WORKDAY(DATE(C2-1,12,31),1)

But it’s not so simple for the US - we can perhaps use CHOOSE function based on the weekday of the date in question, if 1st Jan is any day Monday to Friday we don’t add or subtract, but if it’s a Saturday we subtract one day and if it’s a Sunday we add one day, so that would be:

=CHOOSE(WEEKDAY(DATE(C2,1,1)),1,0,0,0,0,0,-1)+DATE(C2,1,1)

This approach can be used for other holidays like July 4thin the US or Christmas Day in the UK… but there’s one added twist. In the UK when December 25th is at the weekend then the Monday is taken as a holiday from work… so “Boxing day”, which is usually on December 26th, will now be taken on the Tuesday. It’s always the next working day after Christmas Day… and because our formula for Christmas Day will calculate the first working day after December 24th, Boxing Day is simply the second working day after December 24th, i.e.

=WORKDAY(DATE(C2,12,24),2)

screenshot #6

Conclusion

With the aid of the formulas discussed here we can now calculate all US and UK holidays in Excel.

The downloadable workbook contains examples of the formulas discussed and also lists of all US and UK holidays from 2000 to 2099, calculated using those formulas.

download the Excel workbook for this article



barry's icon
barry houdini, november 2013


Top