Calculating date differences in InfoPath using VBScript code

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

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).

Related InfoPath Articles:

• Calculating date and time differences in InfoPath using VBScript
• Calculating work days between two date pickers in InfoPath using VBScript
• Date and time basics in Microsoft Office InfoPath