##### Synopsis

Some holidays occur on the same day every year, like New Year’s Day on January 1^{st} or Independence Day in the USA on July 4^{th}.
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 N^{th} 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)

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.

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

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 2^{nd}, 1900

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
8^{th} of November, 2013 (because the Thursday before the 8^{th} 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 10^{th} of November will clearly be the same weekday as a week earlier
we can use 3^{rd} rather than 10^{th}, 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))

And from that we can extrapolate a generic formula to give us any n^{th} 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 1^{st} 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

Why does this work? Dates are simply formatted numbers in Excel so you can use mathematical functions on them like any
other number. Because 1^{st} Jan 1900 is the equivalent of 1 and 1^{st} 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 28^{th} 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 1^{st} 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 1^{st} January is a Saturday the next working
day is Monday 3^{rd} and that is the holiday… or if January 1^{st} is a Sunday the holiday is on Monday 2^{nd}.

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

So for the UK we can simply take the first working day after the 31^{st} 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 1^{st} 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 4^{th}in the US or Christmas Day in the UK… but there’s one
added twist. In the UK when December 25^{th} is at the weekend then the Monday is taken as a holiday from work…
so “Boxing day”, which is usually on December 26^{th}, 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 24^{th},
Boxing Day is simply the second working day after December 24^{th}, i.e.

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

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.

barry houdini, november 2013