In the previous parts of this series on public holidays we looked at calculating when Easter falls in the year and how to find the next Monday. Now we’ll be trying to find the first and last Monday of a month.
The May Day public holiday falls on the first Monday of May in England. This just requires a slight change to the previous pattern to find the next Monday.
May Day = VAR PH = DATE ( Years[Year], 5, 1 ) RETURN VAR PHWD = WEEKDAY ( PH, 2 ) VAR PHADJ = 8 - PHWD RETURN IF ( PHWD <> 1, PH + PHADJ, PH )
I’m doing the same thing here, finding the adjustment required to calculate the next Monday. The only difference is in my IF statement; before it would only trigger if the date fell on a Saturday or Sunday, now it triggers if the weekday is not a Monday.
Now for the last Monday of the month pattern. In England we have the Spring public holiday which falls on the last Monday of May (this weekend! woo!) and the Summer public holiday is the last Monday of August.
Again this is a very similar pattern:
Spring = VAR PH = DATE ( Years[Year], 5, 31 ) RETURN VAR PHWD = WEEKDAY ( PH, 2 ) VAR PHADJ = 1 - PHWD RETURN IF ( PHWD <> 1, PH + PHADJ, PH )
For my date I’m taking the last day of May instead and my adjustment calculation becomes 1 – weekday number instead of 8 – weekday number. If this day falls on a Monday then it will be 1 – 1 so no adjustment. For any other day it will produce a negative adjustment so we will be going backwards from the reference date rather than forwards.
Like the next Monday pattern, these can be modified to find the first/last Tuesday, Wednesday, etc. by adding 1 to the adjustment for each day after Monday.
For the final part we’ll be looking at how to put these calculations into a more useable format.