Calculate month difference in InfoPath 2010 using rules

Learn how you can use the number and substring functions, rules, conditions, and formulas in InfoPath 2010 to calculate the difference in months between two date picker controls.

ADVERTISEMENTS

Problem

You have two InfoPath date picker controls on a form template and would like to calculate the difference in months between the dates selected in the date pickers.

Solution

Use rules, conditions, formulas, and the number and substring functions in InfoPath 2010 to calculate the month difference between two date pickers.

Discussion

Calculating the difference in months between two date picker controls in InfoPath is similar to calculating the age of a person in InfoPath.

Both solutions make use of the fact that you can extract the year and month from InfoPath date fields using the substring function.

The logic for calculating the month difference between two date pickers in InfoPath is shown in the diagram in Figure 1 at the end of this article.

You can calculate the month difference as follows in InfoPath 2010:

  1. In InfoPath Designer 2010, create a new Blank Form template.
  2. Add two Date Picker controls to the form template and name them startDate and endDate, respectively.
  3. Add a Text Box control to the form template, change it into a Calculated Value control, and name it monthDifference.
  4. Click startDate, and then select Add Rule > Is After > Show Validation Error.
  5. On the Rule Details dialog box, select endDate using the formula button, and click OK.
  6. Click endDate, and then select Add Rule > Is Before > Show Validation Error.
  7. On the Rule Details dialog box, select startDate using the formula button, and click OK.
  8. Add an Action rule to endDate with a Condition that says:

    number(substring(., 1, 4)) - number(substring(../my:startDate, 1, 4)) = 0
    with an action that says:

    Set a field's value: monthDifference = number(substring(., 6, 2)) - number(substring(startDate, 6, 2))
    and select Don't run remaining rules if the condition of this rule is met.
  9. Add a second Action rule to endDate with a Condition that says:

    number(substring(., 1, 4)) - number(substring(../my:startDate, 1, 4)) > 0
    with an action that says:

    Set a field's value: monthDifference = (number(substring(., 1, 4)) - number(substring(startDate, 1, 4))) * 12 + (number(substring(., 6, 2)) - number(substring(startDate, 6, 2)))
    and select Don't run remaining rules if the condition of this rule is met.
  10. Add an Action rule to startDate with a Condition that says:

    number(substring(../my:endDate, 1, 4)) - number(substring(., 1, 4)) = 0
    with an action that says:

    Set a field's value: monthDifference = number(substring(endDate, 6, 2)) - number(substring(., 6, 2))
    and select Don't run remaining rules if the condition of this rule is met.
  11. Add a second Action rule to startDate with a Condition that says:

    number(substring(../my:endDate, 1, 4)) - number(substring(., 1, 4)) > 0
    with an action that says:

    Set a field's value: monthDifference = (number(substring(endDate, 1, 4)) - number(substring(., 1, 4))) * 12 + (number(substring(endDate, 6, 2)) - number(substring(., 6, 2)))
    and select Don't run remaining rules if the condition of this rule is met.
  12. Preview the form.

When the form opens, select a start date and an end date and verify that the month difference is calculated correctly.

Calculating month difference between two date pickers in InfoPath 2010

Figure 1. Calculating month difference between two date pickers in InfoPath 2010.

 
 Subscribe for updates via RSS or Email

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 of S.Y.M. Wong-A-Ton. 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, S.Y.M. Wong-A-Ton.

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 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

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