Calculate work days excluding holidays in InfoPath 2010 using SharePoint 2010 and Excel Services
Learn how you can leverage Excel 2010, Excel Services in SharePoint 2010, and the new REST Web Service data connection in InfoPath 2010 to calculate the difference between two dates in InfoPath 2010 by getting back the amount of work days excluding holidays.
In a previous InfoPath SharePoint article I already wrote about how you can use Excel Services in SharePoint 2007 to perform date calculations in InfoPath 2007.
However, this article performed a standard date difference calculation between two InfoPath date picker controls without taking work days and holidays into account.
Calculating the amount of work days between two dates and account for holidays while doing so can be challenging to achieve in InfoPath without writing code.
But if you have Excel Services in SharePoint 2010 at your disposal you can leverage formulas in Excel 2010 to create a workbook specifically for performing such calculations. In addition, you can use the Excel Services REST API to make REST Web Service calls from within an InfoPath 2010 form to that Excel workbook containing the formulas.
In Excel 2010, you must create a workbook that makes use of a formula to calculate the amount of work days between two dates and take holidays into account.
Excel has a handy formula called NETWORKDAYS that takes a start date, an end date, and an array or range of cells containing the dates for holidays. So all you would have to do is define the dates for the holidays in a range of cells, use this range in the formula, and you are good to go.
Because dates must not be text, but actual dates, you can use the DATE formula in Excel to convert a combination of day, month, and year into a valid date for use in the NETWORKDAYS formula.
Once you have created the Excel workbook containing the formula, you must then upload it to the trusted file location you defined in SharePoint, test that Excel Services can access it and that it is working properly, and then use it in InfoPath 2010.
You may have heard about the new REST Web Service data connection in InfoPath? Well, all you then need to do is create such a data connection to the Excel workbook in Excel Services on SharePoint, and then add rules to call the REST Web Service, pass date values as parameters to be used in the formula, and retrieve the results of the calculation.
The latter is simply a matter of performing basic actions in InfoPath 2010, such as adding conditions, action rules, setting default values, and working with secondary data sources. This should come easy to you if you have already mastered the basics in InfoPath 2010.
This solution also works for InfoPath browser forms. The following InfoPath 2010 demo video shows how it would work:
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. The 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.