Calculating date differences in InfoPath using VBScript code

Use VBScript code to calculate the difference between two date pickers in InfoPath.

ADVERTISEMENTS

Problem

You have two date pickers on an InfoPath form and want to calculate and show the difference between these two dates.

Solution

Use the DateDiff function in VBScript to perform date difference calculations in InfoPath.

Note: You do not have to write code to calculate the difference between two date picker fields in InfoPath, but can use rules, formulas, and functions instead. For more information, see Calculating date differences in InfoPath using rules and formulas - no code!.

Discussion

Suppose you have two date pickers on your InfoPath form: startDate and endDate. For this example, it does not matter whether these fields have the data type Date (date) or Date and Time (dateTime), since only the "date" part (YYYY-MM-DD) of the ISO date string used in InfoPath will be used in your calculation (see Date and time basics in InfoPath).

Before you can calculate the difference between startDate and endDate, you have to convert the ISO date format used in InfoPath into a VBScript Date object and then do the date calculations.

You can accomplish this functionality as follows:

  1. Create a New Blank Form and add a Table with Title layout table to the InfoPath form.
  2. Split the second row of the table into 3 columns.
  3. Add a Date Picker control to the first column, a second Date Picker control to the second column, and a Text Box control to the third column.
  4. Rename the first Date Picker control to startDate, the second Date Picker control to endDate, and the Text Box control to dateDiff.
  5. Switch to the Data Source pane and add an extra Field (element) of type Text (string) to the Main data source. Name it dateInterval.
    Your Main data source should now resemble the following figure:

    Figure 1. Main data source of the InfoPath form.
  6. Modify the table and controls to resemble the following figure:

    Figure 2. The InfoPath form in design mode..
  7. Make sure the language for your InfoPath form is set to VBScript by going to the Tools > Options... menu item, clicking on the Design tab, and setting the Default programming language to VBScript.
  8. Select the Tools > Programming > Microsoft Script Editor menu item to open the Microsoft Script Editor.
  9. Add the following function to convert the ISO date string that is used internally in InfoPath into a VBScript Date object to the end of the script.vbs script file of your InfoPath form.
    Function ISODateStringToVBDate(ISODateString)
      Dim dtRetVal
      dtRetVal = Null

      If Trim(ISODateString) <> "" Then
        dtRetVal = DateSerial(Mid(ISODateString, 1, 4), _
          Mid(ISODateString, 6, 2), Mid(ISODateString, 9, 2))
      End If

      ISODateStringToVBDate = dtRetVal
    End Function
  10. You can calculate the difference between two dates by first converting the InfoPath date into a VBScript date using the custom ISODateStringToVBDate function and then use the VBScript DateDiff function to calculate the difference between the two dates in whatever timespan (years, months, days, hours, minutes, seconds) you like.

    Add the following subroutine at the end of the script.vbs script file of your InfoPath form after the last function you added in the previous step. This subroutine will calculate the difference between the two dates.
    Sub CalculateDateDifference()
      Dim strStartDate
      Dim strEndDate
      Dim strDateInterval
      Dim dtStart
      Dim dtEnd
      Dim intDateDiff

      'Retrieve the ISO date from the InfoPath date fields
      strStartDate = XDocument.DOM.selectSingleNode _
        ("/my:myFields/my:startDate").text
      strEndDate = XDocument.DOM.selectSingleNode _
        ("/my:myFields/my:endDate").text

      'Convert ISO date into VBScript date
      dtStart = ISODateStringToVBDate(strStartDate)
      dtEnd = ISODateStringToVBDate(strEndDate)

      If Not IsNull(dtStart) And Not IsNull(dtEnd) Then

        'Retrieve the date interval to be used
        strDateInterval = XDocument.DOM.selectSingleNode _
          ("/my:myFields/my:dateInterval").text

        Select Case LCase(strDateInterval)
          Case "yyyy"
            'Difference between dates in years
            intDateDiff = DateDiff("yyyy", dtStart, dtEnd)
          Case "m"
            'Difference between dates in months
            intDateDiff = DateDiff("m", dtStart, dtEnd)
          Case "d"
            'Difference between dates in days
            intDateDiff = DateDiff("d", dtStart, dtEnd)
          Case "h"
            'Difference between dates in hours
            intDateDiff = DateDiff("h", dtStart, dtEnd)
          Case "n"
            'Difference between dates in minutes
            intDateDiff = DateDiff("n", dtStart, dtEnd)
          Case "s"
            'Difference between dates in seconds
            intDateDiff = DateDiff("s", dtStart, dtEnd)
          Case Else
            'If interval is not recognized so use difference in days
            intDateDiff = DateDiff("d", dtStart, dtEnd)
        End Select

        'Set the value of the dateDiff field
        XDocument.DOM.selectSingleNode _
          ("/my:myFields/my:dateDiff").text = intDateDiff

      End If

    End Sub
  11. Switch back to Microsoft Office InfoPath 2003 and double-click on the startDate field to open its Properties dialog box. 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 handler for you:
    Sub msoxd_my_startDate_OnAfterChange(eventObj)
    ...
    End Sub
  12. Modify the code for this event handler by adding an extra line of code as follows:
    Sub msoxd_my_startDate_OnAfterChange(eventObj)

    If eventObj.IsUndoRedo Then
    Exit Sub
    End If

    CalculateDateDifference

    End Sub
  13. Switch back to Microsoft InfoPath and click on OK to close each open dialog box.
  14. Repeat steps 11 through 13 for the endDate field.
  15. Go to the Date Source pane and double-click on the dateInterval field to bring up its properties.
  16. Type in one of the following text values for the Default Value of the dateInterval field depending on the date difference calculations you want to display:
    Interval Calculation Default Value of dateInterval field
    Years yyyy
    Months m
    Days d
    Hours h
    Minutes n
    Seconds s
    Note: If the Default Value of the dateInterval field is not set or is not recognized, the date interval will default to a date interval calculation of Days.

You should now have a fully functional InfoPath form that displays the difference between two dates in a specified date interval (years, months, days, hours, minutes, or seconds).

 
 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