SharePoint Irish Holidays - Part 2 (Easter)

Problem: I want to include Easter in my holiday list.

Technology: WSS3.0, Excel 2007, Vista

Facts:

1.       In Western Christianity, Easter is always celebrated on the Sunday immediately following the Paschal Full Moon.

2.       Ireland observes Easter Monday.

Plan of Attack: Create a calculated column using nested if in combination with and and or.

Solution:

1.       From the above definition of Mary Fairchild we mere mortal will not come up with a practical solution.

2.       Fortunately, Chip Pearson gave us a simple readymade formula, i.e. =FLOOR("5/"&DAY(MINUTE(YYYY/38)/2+56)&"/"&YYYY,7)-34. When I modify it to pick up the years from a cell, i.e. =FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34, it works right away. I further tweak it to resolve Easter Monday so it became =FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-33.

3.       But, SharePoint seem not to understand the above formula. As I looked hard at it, I dawned to me that it is just applying FLOOR on a date, such that the year is being fed, the month is fix at 5, and the more complicated computation for the day. So after some more tweaking, I arrived with =FLOOR(DATE(YEAR(sampledate),5,DAY(INT(MINUTE(INT(YEAR(sampledate))/38)/2+56))),7)-34. However, when I tested it with SharePoint, year 2011, 2038, and so on got it wrong.

4.       This put me in a dilemma, I could use this since the next occurrence of the anomaly is in 27 years but it got 2011 wrong, the possible deployment of the project. I certainly will not look good on anyone’s opinion.

5.       I finally opted to hard code the dates so I ended with =IF(OR(VALUE("4/4/2010")=VALUE(sampledate),VALUE("24/4/2011")=VALUE(sampledate)),"Easter","NO")

Final Formula

=IF(AND(MONTH(DOR)=1,DAY(DOR)=1),"New Year",IF(AND(MONTH(DOR)=3,DAY(DOR)=17),"Saint Patrick's Day",IF(AND(MONTH(DOR)=12,DAY(DOR)=25),"Christmas Day",IF(AND(MONTH(DOR)=12,DAY(DOR)=26),"Saint Stephen's Day",IF(AND(MONTH(DOR)=5,WEEKDAY(DOR)=2,DAY(DOR)<7),"Labour Day",IF(AND(MONTH(DOR)=6,WEEKDAY(DOR)=2,DAY(DOR)<7),"June Holiday",""))))))&IF(AND(MONTH(DOR)=8,WEEKDAY(DOR)=2,DAY(DOR)<7),"August Holiday",IF(AND(MONTH(DOR)=10,WEEKDAY(DOR)=2,DAY(DOR)>24),"October Holiday",IF(OR(VALUE(DOR)=INT(VALUE("5/4/2010")),VALUE(DOR)=INT(VALUE("25/04/2011")),VALUE(DOR)=INT(VALUE("09/04/2012")),VALUE(DOR)=INT(VALUE("01/04/2013")),VALUE(DOR)=INT(VALUE("21/04/2014")),VALUE(DOR)=INT(VALUE("06/04/2015")),VALUE(DOR)=INT(VALUE("28/03/2016")),VALUE(DOR)=INT(VALUE("17/04/2017")),VALUE(DOR)=INT(VALUE("02/04/2018")),VALUE(DOR)=INT(VALUE("22/04/2019")),VALUE(DOR)=INT(VALUE("13/04/2020"))),"Easter Monday","")))

This will give my solution a lifetime of 10 years, after which the computing world as we know it will be totally different.