Wednesday, June 1, 2016

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;  
         }