Validate Business Day in InfoPath Date Picker

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.

  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
    •  
  2. 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 BRILLIANTLY came up with this, please let me know so I can give the proper respect/kudos/credit.):

      (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

    4. 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. Select your date field then click OK
      4. Repeat the steps and replace all references of “../my:MyDate”
    5. Click “Verify Formula” and you want to see a no error confirmation like shown below

       
    6. 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)
    •  
  3. 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”

         
      • 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
    •  
  4. 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
       

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, shown in screenshot below, is to leverage an error message and a validation flag on the Date field, should a weekend date is selected by the user.  Here’s the form in Preview mode:

Advertisements

6 responses to “Validate Business Day in InfoPath Date Picker

  1. hi is there anyway of excluding holidays also ? thanks.

    • Yes. Try creating a custom list with a Date field then create a data connection in InfoPath to query that custom list. You can have a conditional formatting rule that excludes date entry/holiday in the custom list.

  2. Thank you so very much, this saved me countless hours of slogging!!

  3. Thanks for your usefull post..With weekends i need to exclude Holidays also..Please let me know….

    • Pankaj, try first creating a custom list with a date field. Then enter all your holidays on that list. In InfoPath, create a data connection to that custom list. Then create a rule in InfoPath that notifies the user (or disables field/control) if the date that user entered in form matches the date from the Holiday SharePoint List. Hope this helps.

  4. Azhar Hussain

    Amazing code in infopath , great

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s