SharePoint End Date on Workday using a Holiday List

Scenario

• Typical scenario where you need to add a certain number of days to a start date and set the due date to a working day (Ex: service window).  Note that this is not "number of working days".  It is "the next working day after X days".
• You can easily adapt this code to provide the last working day before X days.
• The working day must bypass weekends and holidays.
• You have a Holidays list in SharePoint that you can query.
• The Holidays list uses the out of the box "Holiday" list content type.
• Note: In code, the "Holiday" "Date" field's internal name is "V4HolidayDate"
• You need to add some code to an event receiver or custom workflow action to get this date.

Solution

• This was coded for SharePoint 2013 but should work for other versions
  
  

       protected void MAIN_FUNCTION()  
       {  
         DateTime startdate = Convert.ToDateTime(START_DATE_VALUE).Date;  
         DateTime finaldate = startdate.AddDays(Convert.ToInt16(FINAL_DATE_VALUE));  
         SPList holidayLibrary = web.GetList("/Lists/Holidays");  
         Int16 CurrentHolidayIndex = 0;  
  
         SPQuery query = new SPQuery();  
         query.Query = string.Concat(  
                 "<Where><And><Geq>",  
                  "<FieldRef Name='V4HolidayDate'/>",  
                  "<Value Type='DateTime'>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(finaldate) , "</Value>",  
                 "</Geq><Leq>",  
                  "<FieldRef Name='V4HolidayDate'/>",  
                  "<Value Type='DateTime'>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(finaldate.AddDays(14)), "</Value>",  
                 "</Leq></And></Where>",  
                 "<OrderBy>",  
                  "<FieldRef Name='Date' Ascending='FALSE' />",  
                 "</OrderBy>");  
         query.ViewFields = "<FieldRef Name='V4HolidayDate' />";  
         query.ViewFieldsOnly = true;  
         //Limited to 14 days of continuous holidays  
    
         while (IsWeekend(finaldate) || IsHoliday(finaldate, holidays, ref CurrentHolidayIndex))  
         {  
           finaldate = finaldate.AddDays(1);  
         }  
         OUTPUT_FIELD_VALUE = finaldate.ToShortDateString();  
       }  
  
       bool IsWeekend(DateTime date)  
       {  
         if (date.DayOfWeek == DayOfWeek.Saturday || date.DayOfWeek == DayOfWeek.Sunday) return true;  
         return false;  
       }  
  
       bool IsHoliday(DateTime date, SPListItemCollection holidays, ref Int16 holidayIndex)  
       {  
         for (; holidayIndex < holidays.Count; holidayIndex++)  
         {  
           DateTime holiday = Convert.ToDateTime(holidays[holidayIndex]["V4HolidayDate"]);  
           if (date == holiday) return true;  
           if (holiday > date) break; //stop checking once we pass the current date  
         }  
         return false;  
       }