Problem: The Google Search add-on is missing from the Internet Explorer Gallery.


Solution: Go to http://www.iegallery.com/en/addons/detail.aspx?id=813 and add Google as usual.


Source: http://answers.microsoft.com/en-us/ie/forum/ie8-windows_vista/cant-install-google-search-provider/c2d57255-39dc-4203-8461-98266952ffa0

Microsoft Hyper-V Virtual Machine Bus Error

Problem: After I restarted my Windows 2003 guest OS in Hyper-V 2012 host, the network adapter went missing and the Microsoft Hyper-V Virtual Machine Bus exhibited yellow exclamation mark.

Technology: Host is Hyper-V 2012 Server, Guest is Windows 2003 Standard Server running Windows SharePoint Services 3.0, and Hardware is Dell PowerEdge 2950

Solution:

1.      Add legacy network adapter to allow the guest OS network connection.

2.      Do a Windows Update on guest OS.

3.      Uninstall Microsoft Hyper-V Virtual Machine. DO NOT REINSTALL.

4.      Insert the Integration Services Setup Disk from Hyper-V Manager.

5.      Reboot the guest OS.

6.      Allow the guest OS to reinstall an updated driver upon reboot.

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.

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",""))