Saturday, January 16, 2010

Workdays Late/Due Counter Excluding Holidays Part 1

This is the first part of a site I had to design in Sharepoint. The customer was in need of a commitment tracking system with a due status that would display the amount of workdays that an item had left until it was due and also would display the amount of workdays it was late based on the original ECD. They also threw a curve ball by wanting to exclude holidays. I had to think about this and I asked them if the holidays would be location/project based as well. This is mainly because we have work all over the world and different holidays may apply. This was indeed what they had hoped to achieve and I felt good knowing that I understood what they wanted. I was a little hesitant to commit that I could do it, but I really thought it should be possible. I came up with the following solution. Keep in mind there may be a different way to do this but this is what I thought would do the job in the time I had to do it in. Step 1 was to create a blank site definition Sharepoint solution in VS 2008 ( I used VSeWSS 1.3 ) Step 2 was to create the list and add the requested fields including the ECD and Due Status fields. Step 3 was to add another field that would help me do this. I decided to create a computed field that would just be an empty div element with an id based on the ListItem ID and a small token like dsf. This would make a unique empty div for each item on the page. Step 4 was to create a calendar list that would be a holiday calendar. I just added a field for project so that the holidays could either be set to a project or all projects. The customer would then be able to add the holidays as normal events and this will be taken into account later. Step 5 was to actually put all the pieces together. I decided to write a page load event on the pages that displayed this field. Again this is just what I chose to do at the time and I also chose C# as the language. The event basically loops through all the open commitments and checks the current date with the ECD. It determines if the date is before or after today and also checks the project and holiday calendar to see if it needs to remove any days. It also removes weekends! So the loop creates a hidden div with another custom id based on the item and places the divs on the page. This so far does not seem to slow down the system at all. So far so good. Step 6 was to actually display the data to the users. Again I chose to use script for this but I used javascript in this case. The _SPBodyOnLoad function comes in handy here and I used it to loop through all the divs on the page and move the content from the hidden divs to the empty divs based on the unique divs. Well, that is the description of my approach. In part 2 I will bring some code to the table so I can show you the real good stuff! (Well, it works at least!)


  1. HI Daniel,
    I am looking for such solution to exclude holidays from working days. can you please share the code as I am unable to find Part2 of this article.

  2. My apologies for not seeing this sooner! Unfortunately this was part of a previous job and it may take some time to get the code. Also, this was written some time ago and the approach taken then might not be the best solution though it does depend on what version you are on and what you are allowed to do.