SharePoint - Irish Holidays

Problem: I want to filter my list to separate working days from non-working days.

Technology: WSS3.0

Facts:

1.       Non-working days:

a.      Ireland has 9 Public Holidays

i.         4 Fixed Date Holidays (1 January New Year's Day, 17 March Saint Patrick's Day, 25 December Christmas Day, 26 December St. Stephen's Day)

ii.       5 Moveable Mondays (Easter Monday, Labour Day, June Holiday, August Holiday, October Holiday)

b.     Weekends (Saturdays and Sundays)

Plan of Attack:

1.       Create a calculated column for holidays.

2.       Use nested if in combination with and and or.

Solution:

1.       Fixed Date Holidays

a.     For each of the fixed holiday, we only need to determine if the dates falls on certain date and month.

b.     Formula

i.         IF(AND(MONTH(sampledate)=1,DAY(sampledate)=1),"New Year",

ii.       IF(AND(MONTH(sampledate)=3,DAY(sampledate)=17),"Saint Patrick's Day",

iii.     IF(AND(MONTH(sampledate)=12,DAY(sampledate)=25),"Christmas Day",

iv.     IF(AND(MONTH(sampledate)=12,DAY(sampledate)=26),"Saint Stephen's Day",

2.       Moveable Date Holidays

a.      May, June, and August Bank Holidays are observed on the first Monday of these months.  So we need to determine if it is the correct month, if the day is a Monday, and if it is within the first 7 days of the month.

b.     Formula

i.         IF(AND(MONTH(sampledate)=5,WEEKDAY(sampledate)=2,DAY(sampledate)<7),"Labour Day",

ii.       IF(AND(MONTH(sampledate)=6,WEEKDAY(sampledate)=2,DAY(sampledate)<7),"June Holiday"

iii.     IF(AND(MONTH(sampledate)=8,WEEKDAY(sampledate)=2,DAY(sampledate)<7),"August Holiday"

c.     October Bank Holiday falls on the last Monday. So we only need to adjust our formula above to determine if the date falls on the last 7 days of October.

d.    Formula

i.         IF(AND(MONTH(sampledate)=10,WEEKDAY(sampledate)=2,DAY(sampledate)>24),"October Holiday"

e.      Easter Monday is a much more complicated calculation that involves the stars and the moon literally so I will leave it in the next blog.

3.       Weekends

a.       We only need to determine if the date falls on Sunday or Saturday.

b.       However, in my implementation, I have a Weekday column as well as Month and Year columns that I always use for variety of purpose. So I just created another column called WorkingDay that calculated if the date is a weekend or a holiday.

4.       7 Nested If Limitation Workaround

a.        Nine Irish holidays clearly overshoot the nested if limitations.
b.        However, Christophe have a workaround which involve concatenating the more than 7 if statements.

Final Formula (minus Easter)

=IF(AND(MONTH(sampledate)=1,DAY(sampledate)=1),"New Year",IF(AND(MONTH(sampledate)=3,DAY(sampledate)=17),"Saint Patrick's Day",IF(AND(MONTH(sampledate)=12,DAY(sampledate)=25),"Christmas Day",IF(AND(MONTH(sampledate)=12,DAY(sampledate)=26),"Saint Stephen's Day",IF(AND(MONTH(sampledate)=5,WEEKDAY(sampledate)=2,DAY(sampledate)<7),"Labour Day",IF(AND(MONTH(sampledate)=6,WEEKDAY(sampledate)=2,DAY(sampledate)<7),"June Holiday",""))))))&IF(AND(MONTH(sampledate)=8,WEEKDAY(sampledate)=2,DAY(sampledate)<7),"August Holiday",IF(AND(MONTH(sampledate)=10,WEEKDAY(sampledate)=2,DAY(sampledate)>24),"October Holiday",""))