InfoPath Q&A: How do I calculate date differences and skip holidays stored within a SharePoint list?

by S.Y.M. Wong-A-Ton

Bob wrote:

Your article “Calculating work days between two date pickers in InfoPath using VBScript” works great. I would like to know if you can take this one step further – how do you skip holidays where the list of dates is stored in a SharePoint list? Thanks very much.

Hi Bob,

Since you are using VBScript and not a managed .NET language such as VB.NET or C#, you have one relatively easy option. Here are the steps:

  1. Create a secondary data connection/source to the SharePoint list containing the dates for the holidays. Set it to automatically retrieve data when the form opens.
  2. In code, retrieve the XML for the secondary data source. This will contain the dates for the holidays.
  3. Loop through the rows for the dates for the holidays and check whether they fall within the period specified by the start and end dates. While looping through the dates, maintain a counter and increment this counter only if a date falls within the specified period.
  4. After looping through all of the dates, you will have a total number of days for holidays within the specified time period. Subtract this amount from the date difference you previously calculated using the article and you’ll have a new date difference, but then without the dates for the holidays.

Hope this answers your question.

 Subscribe (RSS | Email)

Related Posts

Comments to this post were closed 30 days after it was published.

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. Usage of techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

Working with InfoPath