Calculating Holidays like Victoria Day

I recently was asked how to use an Excel formula to calculate the date for the Canadian holiday Victoria Day.
This holiday is defined as the "Monday on or before May 24th".

To handle this type of calculation, I worked out the following general formula for the "Day of the Week on or before a given Date". The variable DoW below equals 1 for Sunday, 2 for Monday, etc. The Date variable is a reference to a date or a date created using a function like DATE(2010,5,24).

=Date-MOD(WEEKDAY(Date,1)-DoW,7)

2 comments:

Bruno said...

Thanks for the formula, but I just want to point out that Victoria Day is actually the "Monday before May 25th" or, for your formula, the "Monday on or before May 24th". For example, in 2009, Victoria Day was on May 18th - which would be impossible using the "Monday on or before May 25th" formula...

It's a bit of nitpicking, I know... :-)

Jon Wittwer said...

Thanks. I've updated the post.