Supporters of End User
Web

SharePoint: Validate Business Day in InfoPath Date Picker

Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.
Categories:InfoPath; MOSS; WSS 2.0; 2007; 2010

You may also be interested in: The SharePoint Shepherd's Guide for End Users from SharePoint Shepherd

 

Editor's note: Contributor Bobby Chang is a SharePoint consultant. Follow him @bobbyschang

A user in a work setting often needs to exclude weekends and only account for business days when entering information. This post will demonstrate how to design a SharePoint form that determines whether a business day was selected from the date picker.

To better illustrate the steps below, I have attached the InfoPath template form (aka the finished product), if you would prefer to follow the logic from there

  1. Have your Data Field ready
    You’ll need 3 data fields. 1st to capture the date; 2nd to find out the day of the selected date; 3rd to determine whether that day is a business day. For this post, we’ll use these Data Fields as reference:
    • MyDate (Date field) = to capture user input
    • CalculatedDay (Whole Number field) = to determine the day
    • IsBusinessDay (True/False field) = to determine business day
    • Assign a formula as a default value in CalculatedDay field
      1. Click fx in Default Value of the CalculatedDay field
      2. Click “Edit XPath (Advanced)”
      3. Paste the following formula (NOTE: I didn’t come up with this. I found it, but lost the reference. If you know of the person who BRILIANTLY came up with this, please let me know so I can give the proper respect/kudos/credit.):

      4. (number(substring(../my:MyDate, 9, 2)) + number(number(substring(../my:MyDate, 1, 4)) - floor((14 - number(substring(../my:MyDate, 6, 2))) div 12)) + floor(number(number(substring(../my:MyDate, 1, 4)) - floor((14 - number(substring(../my:MyDate, 6, 2))) div 12)) div 4) - floor(number(number(substring(../my:MyDate, 1, 4)) - floor((14 - number(substring(../my:MyDate, 6, 2))) div 12)) div 100) + floor(number(number(substring(../my:MyDate, 1, 4)) - floor((14 - number(substring(../my:MyDate, 6, 2))) div 12)) div 400) + floor(31 * number(number(substring(../my:MyDate, 6, 2)) + 12 * floor((14 - number(substring(../my:MyDate, 6, 2))) div 12) - 2) div 12)) mod 7
        

      5. Replace all of my data source with yours by doing the following:
        1. Highlight the first reference of “../my:MyDate”
        2. Click “Insert Field or Group”
        3. 2012-11-01-InfopathDatePicker-01.jpg

        4. Select your date field then click OK
        5. Repeat the steps and replace all references of “../my:MyDate”
      6. Click “Verify Formula” and you want to see a no error confirmation like shown below
      7. 2012-11-01-InfopathDatePicker-02.jpg

      8. When a date is selected by the user, this field will now generate a number from 0 to 6 that corresponds to a particular day (0 = Sunday; 1 = Monday; 2 = Tuesday; … 6=Saturday)
      • Create a rule to account for Business Day
        1. Select CalculatedDay field
        2. Click Manage Rules on the ribbon
        3. Add a new action “Mark as Business Day”
        4. Create the following Condition:
            CalculatedDay “is greater than or equal to” 1
            AND
            CalculatedDay “is less than or equal to” 5
        5. Add the following action
          • “Set a field’s value”
          • 2012-11-01-InfopathDatePicker-03.jpg

          • Select field of “IsBusinessDay”
          • Assign the value of true
            1. Click fx
            2. Insert Function
            3. Find and select “true”
            4. Click OK
        6. Click OK
        • Create a rule to account for Weekend
          1. Select CalculatedDay field
          2. Click Manage Rules on the ribbon
          3. Copy the Business Day rule from above and change the action name to “Mark as Weekend”
          4. Update the Condition to:
              CalculatedDay “is equal to” 0
              OR
              CalculatedDay “is equal to” 6
          5. Update the action to:
            • Field = “IsBusinessDay”
            • Assign the value of false
              1. Click fx
              2. Insert Function
              3. Find and select “false”
              4. Click OK
          6. Click OK
          7. The CalculateDay field rules should look as follow

          2012-11-01-InfopathDatePicker-04.jpg

        And there you have it.

        You now have a Boolean identifier in the IsBusinessDay field that you can use to validate the date input. One potential scenario is to disable a submit button if the user were to select a non-business-day. Another scenario is one that I have shared in the sample Form Template – there’s an error message and a validation flag on the Date field, should a weekend date is selected by the user. Here’s a screenshot of the form in Preview mode:

        2012-11-01-InfopathDatePicker-05.jpg

        Comments

        i:0e.t|liveid|00067ffe88344cda@live.com

        great post

        Hi You are actually my hero! Thank you very much this post and 5 Steps to Enhance SharePoint 2010 Approval Workflow were so helpful for me!

        Notify me of comments to this article

        E-mail:
           

        Add Comment

        Title:

         
        Comment:
        Email:

           


        Name:

         
        Url: