Calculating date and time differences in InfoPath using VBScript

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

ADVERTISEMENTS

Problem

You have a start date and time and an end date and time in InfoPath, and you want to calculate the difference between these dates and times. For example, if the start date is 2/26/2006 and the start time is 07:00 and the end date is 2/27/2006 the end time is 15:30, the total amount of hours would be 32.5.

Solution

Use the DateDiff VBScript function to perform date 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.
  3. Add a Repeating Table control with 5 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 startDate, the field2 field element to startTime, the field3 field element to endDate, the field4 field element to endTime, and the field5 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. The grandTotal field is shown here in yellow
    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. Convert the startDate and endDate fields to be Date Picker controls, change their data type to Date (date), and set their format to 2001-03-14.
  12. 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.
  13. Make sure that your form is set to use VBScript as the Default programming language by setting this through Tools > Options... and the Design tab before you add the OnAfterChange event handlers in the next step.
  14. Since you want the totalHours and grandTotal fields to be calculated and updated whenever dates are entered into the startDate and endDate fields and times are entered into the startTime and endTime fields, you need to add event handlers to the startDate, startTime, endDate, and endTime fields. Do the following to get this done: Open the properties dialog box of the startDate 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:

    Sub msoxd_my_startDate_OnAfterChange(eventObj)
    ...
    End Sub
  15. Modify the code for this event by adding an extra line as follows:

    Sub msoxd_my_startDate_OnAfterChange(eventObj)

    If eventObj.IsUndoRedo Then
    Exit Sub
    End If

    updateFields(eventObj.Source)

    End Sub
  16. Switch back to Microsoft InfoPath and click on OK to close each open dialog box.
  17. Repeat steps 14 through 16 for the startTime, endDate, and endTime fields.
  18. Copy all of the following code and paste it after the last End Sub. The following code will be responsible for calculating the difference between dates and times:

    Function calcTimeInMinutes(startDateValue, startTimeValue, endDateValue, endTimeValue)

    Dim dtStart
    Dim dtEnd
    Dim intDateDiffMin
    Dim intTimeDiffMin
    Dim intDiffMin

    If Trim(startDateValue) <> "" And Trim(startTimeValue) <> "" And _
    Trim(endDateValue) <> "" And Trim(endTimeValue) <> "" Then

    dtStart = ISODateStringToVBDate(startDateValue)
    dtEnd = ISODateStringToVBDate(endDateValue)

    intDateDiffMin = DateDiff("n", dtStart, dtEnd)

    If intDateDiffMin >= 0 Then
    intTimeDiffMin = MinutesSinceMidnight( _
    CInt(Mid(endTimeValue, 1, 2)), CInt(Mid(endTimeValue, 4, 2))) _
    - MinutesSinceMidnight( _
    CInt(Mid(startTimeValue, 1, 2)), CInt(Mid(startTimeValue, 4, 2)))
    intDiffMin = intDateDiffMin + intTimeDiffMin
    End If

    End If

    calcTimeInMinutes = intDiffMin

    End Function
  19. Save all changes.

You should now have fully functional time difference and grand total calculations in your InfoPath form.

 
 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