How to calculate business days - InfoPath Dev
in

InfoPath Dev

Having trouble finding a blog or post that answers your question? Check out our Custom Search Page

How to calculate business days

Last post 02-17-2013 12:10 PM by Patrick Halstead. 5 replies.
Page 1 of 1 (6 items)
Sort Posts: Previous Next
  • 02-02-2008 04:45 AM

    How to calculate business days

    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
  • 05-14-2009 07:15 AM In reply to

    Re: How to calculate business days

    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());
                }
            }
        }
    }

    Filed under:
  • 07-01-2009 12:43 AM In reply to

    Re: How to calculate business days

    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

  • 02-15-2013 10:21 AM In reply to

    Re: How to calculate business days

     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?

     

  • 02-17-2013 12:10 PM In reply to

    Re: How to calculate business days

    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,

    P

    Patrick Halstead
    InfoPath MVP
Page 1 of 1 (6 items)
Copyright © 2003-2014 Qdabra Software. All rights reserved.
View our Terms of Use.