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:
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... :-)
Thanks. I've updated the post.
Post a Comment