Calculating time differences in InfoPath without custom code

Applies to: InfoPath 2003

Use rules in InfoPath to calculate the difference between start and end times, and sum the time differences to get a grand total amount of hours.

ADVERTISEMENTS

Problem

You have a start time and an end time in an hh:mm format, and you want to calculate the difference between these times. For example, if the start time is 07:00 and the end time is 15:30, the total amount of hours would be 8.5.

Solution

Use rules, conditions, the number() function, and the substring() function within formulas in InfoPath to calculate the time difference between 2 time fields.

Discussion

You can accomplish this functionality as follows:

  1. Create a New Blank Form in InfoPath.
  2. Add a Table with Title to the InfoPath form.
  3. Add a Repeating Table control with 3 columns to the table.
  4. Add a footer to the Repeating Table by opening its Properties dialog box and checking Include footer under the Options section on the Display tab.
  5. Open the Data Source pane and rename the group1 group to timeEntries, the group2 repeating group to timeEntry, the field1 field element to startTime, the field2 field element to endTime, and the field3 field element to totalHours.
  6. Add a new field element with the name grandTotal and data type Decimal (double) to the timeEntries group.
  7. Add a new field element with the name helperField and data type Text (string) to the myFields group.
    Your Main data source should now resemble the following figure:

    Figure 1. Main data source of the InfoPath form.
  8. Drag the grandTotal field element to the Repeating Table and drop it in the footer of the repeating table.
  9. Drag the helperField field element and drop it anywhere on the form.
  10. Modify the table and controls to resemble the following figure:

    Figure 2. The InfoPath form in design mode. The grandTotal field is shown here in yellow.
  11. Change the data type of the totalHours field to Decimal (double) and set the Decimal places property to 1 in the Decimal Format dialog box, which is accessible by clicking on the Format... button on the Text Box Properties dialog box for the totalHours field.
  12. Do the same for the grandTotal field on the InfoPath form.
  13. Make the totalHours field read-only by opening its Properties dialog box, clicking on the Display tab, and checking Read-only under the Options section.
  14. Do the same for the grandTotal field on the InfoPath form.
  15. Change the data type of the startTime and endTime fields to Time (time) and set their format to 09:46. This will prevent an incorrect time format of being entered into these time fields.
  16. Create a rule on the startTime field with an Action that has the following settings:
    Action:
    Set a field's value
    Field:
    totalHours
    Value:
    (((number(substring(../my:endTime, 1, 2)) * 3600) + (number(substring(../my:endTime, 4, 2)) * 60)) - ((number(substring(., 1, 2)) * 3600) + (number(substring(., 4, 2)) * 60))) div 3600
    Note: You must check the checkbox Edit XPath (advanced) on the Insert Formula dialog box to be able to copy and paste the Value above and avoid getting errors.
  17. Create 4 conditions on the rule you created in the previous step that say:
    startTime is not blank
    and
    endTime is not blank
    and
    startTime matches pattern Custom pattern: 00:00:00
    Note: You can enter this condition by selecting startTime from the first dropdown list box in the Condition dialog box, matches pattern from the second dropdown list box, and Select a pattern... from the third dropdown list box and type \d{2}:\d{2}:\d{2} into the Custom pattern field of the Data Entry Pattern dialog box.

    and finally
    (number(substring(../my:endTime, 1, 2)) * 3600 + number(substring(../my:endTime, 4, 2)) * 60 - (number(substring(., 1, 2)) * 3600 + number(substring(., 4, 2)) * 60)) div 3600 >= 0
    Note: You can enter this condition by selecting The expression from the first dropdown list box and then typing in the expression.
  18. Create a rule on the endTime field with an Action that has the following settings:
    Action:
    Set a field's value
    Field:
    totalHours
    Value:
    (((number(substring(., 1, 2)) * 3600) + (number(substring(., 4, 2)) * 60)) - ((number(substring(../my:startTime, 1, 2)) * 3600) + (number(substring(../my:startTime, 4, 2)) * 60))) div 3600
    Note: You must check the checkbox Edit XPath (advanced) on the Insert Formula dialog box to be able to copy and paste the Value above and avoid getting errors.
  19. Create 4 conditions on the rule you created in the previous step that say:
    startTime is not blank
    and
    endTime is not blank
    and
    startTime matches pattern Custom pattern: 00:00:00
    Note: You can enter this condition by selecting startTime from the first dropdown list box in the Condition dialog box, matches pattern from the second dropdown list box, and Select a pattern... from the third dropdown list box and type \d{2}:\d{2}:\d{2} into the Custom pattern field of the Data Entry Pattern dialog box.

    and finally
    (number(substring(., 1, 2)) * 3600 + number(substring(., 4, 2)) * 60 - (number(substring(../my:startTime, 1, 2)) * 3600 + number(substring(../my:startTime, 4, 2)) * 60)) div 3600 >= 0
    Note: You can enter this condition by selecting The expression from the first dropdown list box and then typing in the expression.
  20. Create a rule on the totalHours field with 2 actions for this rule.
    The first Action must say:
    Set a field's value: helperField = ""
    The second Action must say:
    Set a field's value: helperField = "calc"
    Note: The above expressions are the resulting expressions for the actions when you view the Rule dialog box.
  21. Create a rule on the helperField field with an Action that has the following settings:
    Action:
    Set a field's value
    Field:
    grandTotal
    Value:
    sum(../my:timeEntries/my:timeEntry/my:totalHours)
    Note: You must check the checkbox Edit XPath (advanced) on the Insert Formula dialog box to be able to copy and paste the Value above and avoid getting errors.
  22. Remove the helperField field from the form, but NOT from the Data Source. The rule you set previously on the helperField field will remain intact.

You should now have fully functional time difference and grand total calculations in your InfoPath form using only rules, conditions, and formulas with no custom code.

 


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