Calculating time differences in InfoPath using custom JScript code

Use JScript code 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 in InfoPath. 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 the getTime() method of the Date object in JScript code to perform time difference calculations in InfoPath.

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 template.
  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 an extra field element with the name grandTotal and data type Decimal (double) to the timeEntries group.
    Your Main data source should now resemble the following figure:
    Main data source of the InfoPath form
    Figure 1. Main data source of the InfoPath form.
  7. Drag the grandTotal field element to the Repeating Table and drop it in the footer of the repeating table.
  8. Modify the table and controls to resemble the following figure:
    The InfoPath form in design mode
    Figure 2. The InfoPath form in design mode. The grandTotal field is shown here in yellow.
  9. 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.
  10. Do the same for the grandTotal field on the InfoPath form.
  11. 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.
  12. Since you want the totalHours and grandTotal fields to be calculated and updated whenever times are entered into the startTime and endTime fields, you need to add event handlers to the startTime and endTime fields. Do the following to get this done: Open the properties dialog box of the startTime field. Click on the Data Validation... button on the Data tab. Select OnAfterChange from the Events dropdown list box on the Data Validation dialog box. Click on the Edit... button behind the Events dropdown list box. This will open Microsoft Script Editor and add the following event for you:
    function msoxd_my_startTime::OnAfterChange(eventObj)
    {
    ...
    }
  13. Modify the code for this event by adding an extra line of code as follows:
    function msoxd_my_startTime::OnAfterChange(eventObj)
    {
    if (eventObj.IsUndoRedo)
    {
    return;
    }
    updateFields(eventObj.Source);
    }
  14. Switch back to Microsoft Office InfoPath and click on OK to close each open dialog box.
  15. Perform steps 12 through 14 for the endTime field.
  16. Copy all of the following code and paste it after the last closing curly bracket (}) of function msoxd_my_endTime::OnAfterChange(eventObj). The following code will be responsible for calculating the difference between times:
    function calcTimeInMinutes(startTimeValue, endTimeValue)
    {
    var elapsed = 0;

    if (startTimeValue != "" && endTimeValue != "")
    {
    var arrStart = startTimeValue.split(":");
    var arrEnd = endTimeValue.split(":");

    if (arrStart.length >= 2 && arrEnd.length >= 2)
    {
    var dtStart = new Date(1970, 1, 1,
    parseFloat(arrStart[0]), parseFloat(arrStart[1]), 0);
    var dtEnd = new Date(1970, 1, 1,
    parseFloat(arrEnd[0]), parseFloat(arrEnd[1]), 0);

    elapsed = (dtEnd.getTime() - dtStart.getTime()) / 1000;
    }
    }

    return elapsed;
    }
  17. Save all changes.

You should now have fully functional time difference and grand total calculations in your InfoPath form. Follow the instructions and comments included in the JScript file should you want to modify the code further to suit your specific needs.

 
 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