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!!