Calculate business days between two dates excluding holidays using Excel Services
In this InfoPath demo video you will see how you can take a codeless InfoPath browser form and call Excel Services in SharePoint 2010 to calculate the amount of business days between two dates excluding holidays and weekends.
In recipe 98, you will learn how to calculate the amount of workdays between two dates excluding holidays and weekends in InfoPath 2010 using Excel Services in SharePoint 2010.
Get more solutions from InfoPath 2010 Cookbook 2 which is available on CreateSpace (An Amazon company)
Watch next video: Add event to SharePoint calendar through InfoPath 2010
InfoPath demo video transcript:
The InfoPath form that I'm going to be using in this demonstration is not the same form that recipe 98 creates, but it uses the exact same techniques to calculate the amount of work days between two dates excluding holidays and weekends.
I've already published the InfoPath form template to SharePoint, and I've also already published an Excel workbook to SharePoint.
So if I add a new form, you'll see that the form is a simple leave request form. It starts up displaying the current date and the current time. There are fields here in which you can enter your details and your manager's details. And then there is this section here that contains a repeating table in which you can enter lines to request leave.
The intention is that you can select a start date, select an end date (so define the period in which you want to take leave), select the type of leave you want to request (so you can request "Paid vacation", "Sick leave", etc.), and then this form would go off and call Excel Services to calculate the amount of work days between the two dates you selected, exclude any holidays that fall within that period of time and weekends, and then return the amount of days as an amount of hours. And the hours are calculated based on an eight-hour work day.
Excel Services will be using this Excel workbook that contains a short list of dates for holidays. And as you can see, the next upcoming holiday is on the 5th of September.
So let's say we want to book leave around that date. The 5th of September is a Monday, so let's say we want a long weekend. So we'll start our vacation on the 2nd, and it will end on the 6th, which would cost us including weekends and the 5th of September (which is the holiday), it would cost us 2 days. So Excel Services should return 16 hours, which it does (2 times 8).
And this 16 hours is then copied over to this table, and our new leave balance is calculated here. Our previous was 0, which is why we've got a minus 16 there. So let me enter a previous of 120 hours. And then we see that the new balance is calculated there.
Let me enter a second line. Let's say we want to book sick leave. And we were sick last week starting from Thursday until this week Tuesday, which would make 4 days of leave. 4 times 8 is 32. You see the 32 appear there. 32 is copied over here. And with a starting balance of 80 hours, we've got a new balance of 48 sick leave.
Let's enter a second line for paid vacation. And let's say we want to book in December. You can see here that the 26th of December is a holiday. So let's say we want leave starting from the 22nd of December until the 1st of January. And the 1st of January is a Sunday. So including weekends and the 26th, which is a holiday, it would cost us 6 days of leave, which is 6 times 8 is 48. 48 should appear there.
And then we've got one line here of paid vacation and 16 hours, a second line with 48 hours, so 48 and 16 is 64. Then you'll see 64 appear there and our new leave balance calculated.
So in this demonstration you saw how you can take a codeless InfoPath form, which is what this form is with all the internal calculations that it currently performs, and call Excel Services to do the heavy-duty calculations, which would otherwise require code, and return the results to this InfoPath form for display.
Related InfoPath Demo Videos:
- Convert InfoPath form to Word via SharePoint
- InfoPath SharePoint Search form
- Create InfoPath form through SharePoint Designer 2010 workflow
- Link InfoPath form to Excel in SharePoint 2010
- Add event to SharePoint calendar through InfoPath 2010
- Connect SharePoint form library form to SharePoint list form through SharePoint web part connections
- Extract emails from people picker in InfoPath 2010
- Display Access 2010 data in an InfoPath 2010 browser form
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.