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.
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.
Use the getTime() method of the Date object in JScript code to perform time difference calculations in InfoPath.
You can accomplish this functionality as follows:
- Create a New Blank Form in InfoPath.
- Add a Table with Title to the InfoPath form template.
- Add a Repeating Table control with 3 columns to the table.
- 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.
- 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.
- 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:
Figure 1. Main data source of the InfoPath form.
- Drag the grandTotal field element to the Repeating Table and drop it in the footer of the repeating table.
- 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.
- 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.
- Do the same for the grandTotal field on the InfoPath form.
- 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.
- 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:
- Modify the code for this event by adding an extra line of code as follows:
- Switch back to Microsoft Office InfoPath and click on OK to close each open dialog box.
- Perform steps 12 through 14 for the endTime field.
- 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), parseFloat(arrStart), 0);
var dtEnd = new Date(1970, 1, 1,
parseFloat(arrEnd), parseFloat(arrEnd), 0);
elapsed = (dtEnd.getTime() - dtStart.getTime()) / 1000;
- 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.
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.
Related InfoPath Articles:
- Calculate the difference between two date picker controls in InfoPath using rules and formulas - no code!
- Calculating time differences in InfoPath without custom code
- Date and time basics in Microsoft Office InfoPath
- InfoPath 2010 date and time picker control basics
- Dates and time zones in InfoPath 2010