Forcing users to select a particular day of the week from a date picker in InfoPath using rules and formulas

Applies to: InfoPath 2007

Use data validation, rules, and the number(), floor(), and substring() functions to derive and check the day of the week when a date is selected from a date picker control in InfoPath.

ADVERTISEMENTS

Problem

You have a form with an InfoPath date picker control and would like to force users to select only those dates from the date picker that fall on a Friday.

Solution

Use data validation, rules, and the number(), floor(), and substring() functions to derive and check the day of the week when a date is selected from a date picker control in InfoPath.

Discussion

You can accomplish this functionality as follows:

  1. Design an InfoPath form as shown in figure 1 with one Date Picker control named myDate and one Text Box control named dayOfWeekNo.

    Figure 1. InfoPath form template in Design mode.

    The Main data source should resemble the following figure:


    Figure 2. The Main data source of the form template.

    Note: You can remove the dayOfWeekNo field from the view of the form template, but leave its corresponding field in the Main data source of the form template intact, because the latter is used to store the results of the day of the week calculation.
  2. Double-click the myDate field to open its Properties dialog box.
  3. On the Data tab, click Rules.
  4. On the Rules dialog box, click Add.
  5. On the Rule dialog box, click Add Action.
  6. On the Action dialog box, specify the following settings:
    Action: Set a field's value
    Field: dayOfWeekNo
    Value:
    (number(substring(., 9, 2)) + floor(((153 * (number(substring(., 6, 2)) + (12 * floor((14 - number(substring(., 6, 2))) div 12)) - 3)) + 2) div 5) + (365 * (number(substring(., 1, 4)) + 4800 - floor((14 - number(substring(., 6, 2))) div 12))) + floor((number(substring(., 1, 4)) + 4800 - floor((14 - number(substring(., 6, 2))) div 12)) div 4) - 32083) mod 7


    The meaning of the value of the dayOfWeekNo field is defined as follows:
    Value Day
    0 Tuesday
    1 Wednesday
    2 Thursday
    3 Friday
    4 Saturday
    5 Sunday
    6 Monday
  7. Click OK to close each dialog box that is open.
  8. Double-click the myDate field to open its Properties dialog box.
  9. On the Data tab, click Data Validation.
  10. On the Data Validation dialog box, click Add.
  11. On the Data Validation dialog box, select dayOfWeekNo from the first drop-down list box, select is not equal to from the second drop-down list box, select Type a number from the third drop-down list box, and type 3 in the text box.
  12. On the Data Validation dialog box, in the ScreenTip field, type the text "You must select a Friday!", and type this same text in the Message field.
  13. Click OK to close each dialog box that is open.

You should now have a fully functional InfoPath form that will display a red border around the date picker field if a user selects a date that is not a Friday. This solution also works in InfoPath 2003 form templates and InfoPath 2007 browser-enabled form templates.

 


Related InfoPath Articles:

 

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

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services