Code

Re: How to calculate business days

Patrick Halstead — Sun, 17 Feb 2013 20:10:20 GMT

Hi AstromanDan,

I did a webinar last Thursday on how to handle holidays. http://youtu.be/MwejeH898vI

One of the scenarios was holidays.

It does use our qRules common library and you can download a free trial of that from the Qdabra dot com site.

qRules is a common library we created to help save InfoPath form designers time.

We do sell it for a small fee and all proceeds go to supporting this free forum.

Thanks,

Re: How to calculate business days

astromandan — Fri, 15 Feb 2013 18:21:24 GMT

Very nice and concise. I find a lot of scripts around that do this, but none that take into account a list of holidays. Any ideas on mods for this script that would allow a list of dates for holidays?

Re: How to calculate business days

cutty_sark_gr — Wed, 01 Jul 2009 07:43:18 GMT

Thanks for the code,

I just used it and I want to do a correction in the c# if somebody else wants to use it too:

replace the Round function with Floor

thanks again

Re: How to calculate business days

Alec Pojidaev — Fri, 22 May 2009 14:17:09 GMT

Same with rules only: http://alecpojidaev.wordpress.com/2008/12/30/infopath-codeless-programming-walkthrough-2/

C#:

http://alecpojidaev.wordpress.com/2009/10/29/work-days-calculation-with-c/

Re: How to calculate business days

petro@SL — Thu, 14 May 2009 14:15:47 GMT

I've got here the solution based on yours and developed with C# (Thanks so much for your code, it really helped me out!). For some reason, there isn't really any good example in the internet on how to manage this issue with C#, that's why I decided to show the full code here.

I present here a solution for counting business days for a planned vacation:

using Microsoft.Office.InfoPath;
using System;
using System.Xml;
using System.Xml.XPath;

namespace Urlaubsanträge
{
    public partial class FormCode
    {
        DateTime vacationDateStart, vacationDateEnd;
        int dateDifference = 0, businessDays = 0;
        // XPath navigators
        XPathNavigator root, node;

        /// <summary>
        ///
        /// </summary>
        public void InternalStartup()
        {
            EventManager.XmlEvents["/my:Urlaubsantrag/my:Urlaubsanfang"].Changed += new XmlChangedEventHandler(Urlaubsanfang_Changed);
            EventManager.XmlEvents["/my:Urlaubsantrag/my:Urlaubsende"].Changed += new XmlChangedEventHandler(Urlaubsende_Changed);

            // define XPath navigator
            root = MainDataSource.CreateNavigator();
            node = root.SelectSingleNode("/my:Urlaubsantrag/my:ArbeitstageAnzahl", NamespaceManager);
        }

        /// <summary>
        /// On change event for vacation start
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public void Urlaubsanfang_Changed(object sender, XmlEventArgs e)
        {
            // Get start date serial number
            vacationDateStart = DateTime.Parse(e.Site.Value);

            if (vacationDateEnd != null)
            {
                // Calculate difference
                TimeSpan span = vacationDateEnd.Subtract(vacationDateStart);

                dateDifference = span.Days;
                // // How many complete weeks between start and end dates
                // // Multiply by 5 to get the number of business days
                businessDays = (int)Math.Round((decimal)dateDifference / 7, 0) * 5;
                // // Use % operator to get the remaining days
                // // Loop through remaining days to add up business days
                for (int i = 0; i < dateDifference % 7 + 1; i++)
                {
                    // determine the day of week
                    DayOfWeek day = vacationDateStart.AddDays(i).DayOfWeek;

                    // if business day, increment counter
                    if (!day.ToString().Equals("Saturday") && !day.ToString().Equals("Sunday"))
                        businessDays++;
                }

                // if business days greater than 0, print
                if (businessDays >= 0)
                    node.SetValue(businessDays.ToString());
            }
        }

        /// <summary>
        /// On change event for vacation end
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public void Urlaubsende_Changed(object sender, XmlEventArgs e)
        {
            // Get start date serial number
            vacationDateEnd = DateTime.Parse(e.Site.Value);

            if (vacationDateEnd != null)
            {
                // Calculate difference
                TimeSpan span = vacationDateEnd.Subtract(vacationDateStart);

                dateDifference = span.Days;
                // // How many complete weeks between start and end dates
                // // Multiply by 5 to get the number of business days
                businessDays = (int)Math.Round(dateDifference / 7.0, 0) * 5;
                // // Use % operator to get the remaining days
                // // Loop through remaining days to add up business days
                for (int i = 0; i < dateDifference % 7 + 1; i++)
                {
                    // determine the day of week
                    DayOfWeek day = vacationDateStart.AddDays(i).DayOfWeek;

                    // if business day, increment counter
                    if (!day.ToString().Equals("Saturday") && !day.ToString().Equals("Sunday"))
                        businessDays++;
                }

                // if business days greater than 0, print
                if (businessDays >= 0)
                    node.SetValue(businessDays.ToString());
            }
        }
    }
}

How to calculate business days

10basetom — Sat, 02 Feb 2008 12:45:19 GMT

Since I've been getting questions on how to extend my simple date range validation code to support business days, I thought I'd write another article to explain one way of accomplishing this using minimal code and CPU cycles. For those coming from an Excel background, this is similar to the NETWORKDAYS() function that comes with the Analysis ToolPak (InfoPath 2003 would be *THAT* much more powerful if it supports only half of Excel's built-in functions :-)).

First, I suggest you read my earlier post, Date range validation using less code. Calculating total days between two dates is a prerequisite to calculating just business days. Continuing on with the same form, we'll add a new hidden field called diffb; this field will hold the number of business days.

When I first went about trying to figure out how to code this, the first thought that went to my head was to brute force it by looping through each day from the start date till the end date, determining what day of the week it was, and incrementing the business day counter only when it's Mon-Fri (days 1-5 in JavaScript speak, with 0 being Sunday). However, I generally try to avoid using big loops in my code, so I had to rethink my solution.

In the end, I settled on a solution that used a loop with a maximum six iterations. Basically, we figure out how many whole weeks + remaining days comprise the given date range. There are always five business days in a whole week (unless you want to get fancy and add an array for holidays), so we multiply the number of weeks by 5 to get the subtotal for the number of business days; then we loop through the remaining days to add just the business days to get the grand total number of business days. Finally, we output this to the diffb field to set up our data validations.

Here's the complete code with liberal commenting:

var nDateStart, nDateEnd;
var sDateStart, sDateEnd;
var nDiff, nDiffB;
var oDiff = XDocument.DOM.selectSingleNode('my:myFields/my:diff');
var oDiffB = XDocument.DOM.selectSingleNode('my:myFields/my:diffb');

function msoxd_my_start_date::OnAfterChange(eventObj) {
// Normalize date format (yyyy-mm-dd -> mm-dd-yyyy)
sDateStart = eventObj.Site.text.replace(/(\d{4})-(.+)/,'$2-$1');
if (sDateStart) {
    // Get start date serial number
    nDateStart = Date.parse(sDateStart);
    if (nDateEnd) {
      // Calculate difference (86400000 milliseconds = 1 day)
      nDiff = (nDateStart) ? (nDateEnd-nDateStart)/86400000 : '';
      // How many complete weeks between start and end dates
      // Multiply by 5 to get the number of business days
      nDiffB = (nDiff) ? Math.floor(nDiff/7)*5 : '';
      // Use % operator to get the remaining days
      // Loop through remaining days to add up business days
      for (var i=1, n; i<nDiff%7+1; i++) {
        n = new Date(Date.parse(sDateStart)+86400000*i).getDay();
        if (n>0 && n<6) nDiffB++;
      }
      oDiff.text = nDiff;
      oDiffB.text = nDiffB;
    }
}
}

function msoxd_my_end_date::OnAfterChange(eventObj) {
// Normalize date format (yyyy-mm-dd -> mm-dd-yyyy)
var sDateEnd = eventObj.Site.text.replace(/(\d{4})-(.+)/,'$2-$1');
if (sDateEnd) {
    // Get end date serial number
    nDateEnd = Date.parse(sDateEnd);
    if (nDateStart) {
      // Calculate difference (86400000 milliseconds = 1 day)
      nDiff = (nDateEnd) ? (nDateEnd-nDateStart)/86400000 : '';
      // How many complete weeks between start and end dates
      // Multiply by 5 to get the number of business days
      nDiffB = (nDiff) ? Math.floor(nDiff/7)*5 : '';
      // Use % operator to get the remaining days
      // Loop through remaining days to add up business days
      for (var i=1, n; i<nDiff%7+1; i++) {
        n = new Date(Date.parse(sDateStart)+86400000*i).getDay();
        if (n>0 && n<6) nDiffB++;
      }
      oDiff.text = nDiff;
      oDiffB.text = nDiffB;
    }
}
}

Cheers,
Tom