SharePoint Experts, Information Architects, Expert Witness

SICG provides a broad array of business and technology consulting from architecture to design to deployment of global systems with a focus on surfacing data in the enterprise. We focus on the "How", not just the possible. Contact me direct: david_sterling@sterling-consulting.com or call 704-873-8846 x704.

Search This Blog

Thursday, October 24, 2019

Calculating End Date by Business Days, Holidays or Both

I've always had issues trying to calculate dates and I've always forgotten to keep the code somewhere. It's easy to calculate based business days, but holidays are always a pain. I post this here to help you out as well as keep a record of this myself!

So this is C# and pretty simple stuff - works like a charm.

In your code, first you define a Class to enable you to create a list of Holidays from whatever source. This has two elements - the Date and the Number of Days to apply:

public class HolidayDate
{
    public DateTime HolidayDt;
    public int AddDaysForHoliday;
}
You can populate a list using a source like SQL or you can simply set the dates as so (and just in case you are wondering, to use List, you need to add a reference to System.Collections.Generic, I just hate when people don't include that):

public List<HolidayDate> PopulateHolidays()
{
    List<HolidayDate> ListOfHolidays = new List<HolidayDate>();

    HolidayDate VeteransDay = new HolidayDate();
    VeteransDay.HolidayDt = new DateTime(2019, 11, 11);
    VeteransDay.AddDaysForHoliday = 1;
    ListOfHolidays.Add(VeteransDay);

    HolidayDate ThanksGiving = new HolidayDate();
    ThanksGiving.HolidayDt = new DateTime(2019, 11, 28);
    ThanksGiving.AddDaysForHoliday = 2;
    ListOfHolidays.Add(ThanksGiving);

    HolidayDate ShutDownHoliday = new HolidayDate();
    ShutDownHoliday.HolidayDt = new DateTime(2019, 11, 04);
    ShutDownHoliday.AddDaysForHoliday = 5;
    ListOfHolidays.Add(ShutDownHoliday);

    HolidayDate ChristmasDay = new HolidayDate();
    ChristmasDay.HolidayDt = new DateTime(2019, 12, 25);
    ChristmasDay.AddDaysForHoliday = 1;
    ListOfHolidays.Add(ChristmasDay);

    HolidayDate NewYears = new HolidayDate();
    NewYears.HolidayDt = new DateTime(2019, 12, 31);
    NewYears.AddDaysForHoliday = 2;
    ListOfHolidays.Add(NewYears);

    return ListOfHolidays;
}

Notice that I add the number of the days for the Holiday - for example, the Shutdown time in this case is for a week - so setting the Holiday as 11/4 for 5 days blocks out the entire week.

So here are the primary methods:

public DateTime CalcDaysWithHolidaysOnly(DateTime StartDt, int DaysToAdd)
{
    List<HolidayDate> ListOfHolidayDates = new List<HolidayDate>();
    ListOfHolidayDates = PopulateHolidays();
    //
    int AddToDateDays = 0;
    //
    DateTime EndDate = StartDt.AddDays(DaysToAdd);
    DateTime TestDate = StartDt;
    //
    for (int AddDay = 0;AddDay < DaysToAdd; AddDay++)
    {
        //
        // Move forward for holiday:
        //
        AddToDateDays = 1;
        foreach (HolidayDate aHoliday in ListOfHolidayDates)
        {
            if (TestDate.Date.AddDays(1) == aHoliday.HolidayDt.Date)
            {
                int GetDaySpread = aHoliday.AddDaysForHoliday;
                if (GetDaySpread == 1)
                {
                        AddToDateDays++;
                }
                else
                {
                    for (int i = 0; i < GetDaySpread; i++)
                    {
                          AddToDateDays++;
                    }
                }
                break;
            }
        }
        TestDate = TestDate.AddDays(AddToDateDays);
        //Console.WriteLine("Test Date: " + TestDate.ToShortDateString());
    }
    //
    // Final Date:
    //
    EndDate = TestDate;
    //
    return EndDate;
}
public DateTime CalcBusinessDaysWithHolidays(DateTime StartDt, int DaysToAdd)
{
    List<HolidayDate> ListOfHolidayDates = new List<HolidayDate>();
    ListOfHolidayDates = PopulateHolidays();
    //
    int AddToDateDays = 0;
    //
    DateTime EndDate = StartDt.AddDays(DaysToAdd);
    DateTime TestDate = StartDt;
    //
    for (int AddDay = 0; AddDay < DaysToAdd; AddDay++)
    {
        //
        // Move forward for Non-Business Days:
        //
        AddToDateDays = 1;
        if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Saturday)
        {
            AddToDateDays = AddToDateDays + 2;
        }
        else if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Sunday)
        {
            AddToDateDays = AddToDateDays + 1;
        }
        //
        // Move forward for holiday:
        //
        foreach (HolidayDate aHoliday in ListOfHolidayDates)
        {
            if (TestDate.Date.AddDays(AddToDateDays) == aHoliday.HolidayDt.Date)
            {
                int GetDaySpread = aHoliday.AddDaysForHoliday;
                if (GetDaySpread == 1)
                {
                    if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Saturday)
                    {
                        AddToDateDays = AddToDateDays + 2;
                    }
                    else if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Sunday)
                    {
                        AddToDateDays = AddToDateDays + 1;
                    }
                    else
                    {
                        AddToDateDays++;
                    }
                }
                else
                {
                    for (int i = 0; i < GetDaySpread; i++)
                    {
                        if (TestDate.Date.AddDays(AddToDateDays + i).DayOfWeek == DayOfWeek.Saturday)
                        {
                            AddToDateDays = AddToDateDays + i + 2;
                        }
                        else if (TestDate.Date.AddDays(AddToDateDays + i).DayOfWeek == DayOfWeek.Sunday)
                        {
                            AddToDateDays = AddToDateDays + i + 1;
                        }
                        else
                        {
                            AddToDateDays++;
                        }
                    }
                }
                break;
            }
        }
        TestDate = TestDate.AddDays(AddToDateDays);
        //Console.WriteLine("Test Date: " + TestDate.ToShortDateString());
    }
    //
    // Final Date:
    //
    EndDate = TestDate;
    //
    if (EndDate.Date.DayOfWeek == DayOfWeek.Saturday)
    {
        EndDate = EndDate.AddDays(2);
    }
    if (EndDate.Date.DayOfWeek == DayOfWeek.Sunday)
    {
        EndDate = EndDate.AddDays(1);
    }
    return EndDate;
}
public DateTime CalcBusinessDaysOnly(DateTime StartDt, int DaysToAdd)
{
    List<HolidayDate> ListOfHolidayDates = new List<HolidayDate>();
    ListOfHolidayDates = PopulateHolidays();
    //
    int AddToDateDays = 0;
    //
    DateTime EndDate = StartDt.AddDays(DaysToAdd);
    DateTime TestDate = StartDt;
    //
    for (int AddDay = 0; AddDay < DaysToAdd; AddDay++)
    {
        //
        // Move forward for Non-Business Days:
        //
        AddToDateDays = 1;
        if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Saturday)
        {
            AddToDateDays = AddToDateDays + 2;
        }
        else if (TestDate.Date.AddDays(AddToDateDays).DayOfWeek == DayOfWeek.Sunday)
        {
            AddToDateDays = AddToDateDays + 1;
        }
        TestDate = TestDate.AddDays(AddToDateDays);
        Console.WriteLine("Test Date: " + TestDate.ToShortDateString());
    }
    //
    // Final Date:
    //
    EndDate = TestDate;
    //
    if (EndDate.Date.DayOfWeek == DayOfWeek.Saturday)
    {
        EndDate = EndDate.AddDays(2);
    }
    if (EndDate.Date.DayOfWeek == DayOfWeek.Sunday)
    {
        EndDate = EndDate.AddDays(1);
    }
    return EndDate;
}

Calling is just as simple:

DateTime StartDt = new System.DateTime(2019, 10, 29);
int DaysToAdd = 60;
DateTime EndingDate = CalcDaysWithHolidaysOnly(StartDt, DaysToAdd);
DateTime EndingDateBus = CalcBusinessDaysWithHolidays(StartDt, DaysToAdd);
DateTime EndingDateBusOnly = CalcBusinessDaysOnly(StartDt, DaysToAdd);

So hopefully this helps you - if anyone has a better routine, I'd like to see it!!



Thursday, May 16, 2019

Removing the Default Documents Library in SharePoint

So you want to delete the default Documents (or Shared Documents) library - which you should but in many site templates it cannot be deleted (the Delete this library link isn't shown). 

Quick and easy, do it in PowerShell:

# Set Site URL, i.e. http://<site>, http://site/sites/abc, https://, etc.
$theWeb = Get-SPWeb <Site URL>
# Display the object (will display the URL to the site)
$theWeb
$list = $theWeb.Lists[“Documents”]
# Display List Details
$list
# Make it deletable
$list.AllowDeletion = $True
# Update it
$list.Update()
# Now delete it
$list.Delete()

PS: This is also a reminder for myself - I know what to do just forget sometimes!

Wednesday, January 16, 2019

Setting Time Servers (Windows)

A note here for future reference! You might find yourself off time on a server, laptop, pc, etc. and it is a great idea to sync with a known time service. While you have to have admin rights on the individual box (or VM), you can set the time servers using an elevated Command prompt (i.e. Run as Administrator).

I happen to use the NTP servers in the US for synching all of my servers. So - how to set these? A few simple commands - stop the time service, set the sync option with server list, set as reliable and start it back up - the last two commands show you the configuration and status.

Open a command prompt (cmd) using Run as administrator and enter the following (careful, second line is wrapped):

net stop w32time
w32tm /config /syncfromflags:manual /manualpeerlist:"0.us.pool.ntp.org,1.us.pool.ntp.org,2.us.pool.ntp.org,3.us.pool.ntp.org"
w32tm /config /reliable:yes
net start w32time

w32tm /query /configuration
w32tm /query /status


All set!


Wednesday, January 9, 2019

Active Directory Error 0X800708C5

I get questions about this error all the time - the answer is usually very simple - the password you are attempting to apply does not meet the criteria required.

Hope that helps!