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!

Saturday, November 24, 2018

The timer service failed to recycle

This is one I see all the time on client sites - under Review problems and solutions you see the message "The timer service failed to recycle". The problem is actually due to running other jobs that conflict with the recycle process - in short, something else is running.

Many have a habit of having long running jobs start after Midnight (00:00) which is generally around the time the Recycle job runs - if something else is running, the job fails. The solution is quite simple - either A) review and modify jobs that run at the same time as the Recycle job and make sure they don't overlap or B) simply change the time that the recycle happens. Since A may take some time, B is quite simple.

Open Central Administration and click Monitoring then Review job definitions and look for "Timer Service Recycle" (you'll have to page through to find it). Click to open the job and change the Starting time to be either earlier or later than other long running jobs. In most cases (depending on the installation and/or 24 x 7 usage), you can usually set it for late evening (like 10 PM).

For more reference, see here:
https://docs.microsoft.com/en-us/sharepoint/technical-reference/the-timer-service-failed-to-recycle

SharePoint Most Popular does not appear in Lists

As we all know, document libraries have the option to review Most Popular items. While there can be issues with this (showing no counts for example), it does work in most cases. Assuming you are getting counts, you might notice that this option does not appear in Lists.

Enabling this is easy enough - simply activate the Cross Site Publishing feature - Site Settings > Site Collection Features > Cross-Site Collection Publishing.

Saturday, November 10, 2018

ULS Logs Searching for the Correlation ID

Having forgotten about this myself, I am posting here. In many cases, there's instances where you want to scan across ULS in SharePoint for a single Correlation ID. Notepad/Notepad++ are not much help here since messages are mixed. The ULS Viewer isn't always the best either.

The proper way to help you narrow down what you are looking for is "Merge-SPLogFile". This little gem searches across the ULS logs and helps you dump it into a single file.

Format is simple (obviously run one of the farm servers):

Merge-SPLogFile - Path <File Path> -Correlation <Correlation ID> -Overwrite

Overwrite is optional - it simply overwrites an existing file. So for example:

Merge-SPLogFile -Path "c:\temp\MyLogsMerged.log" -Correlation 7d88815d-17d5-412d-a71d-f0d124c8ad7c -Overwrite

And you can add the date (or time):

Merge-SPLogFile -Path "c:\temp\MyLogsMerged-$(Get-Date -f yyyy-MM-dd).log" -Correlation 7d88815d-17d5-412d-a71d-f0d124c8ad7c -Overwrite

Now you can use this merged log file in the ULS viewer so you can take your time to review an entire event without having to filter.

There are actually a lot of things you can use to filter - Correlation is just one. It can be an Event ID, service name, etc. - see the details here:

Merge-SPLogFile on docs.microsoft.com