Calculating date differences in InfoPath using VBScript code
Use VBScript code to calculate the difference between two date pickers in InfoPath.
You have two date pickers on an InfoPath form and want to calculate and show the difference between these two dates.
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!.
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:
- Create a New Blank Form and add a Table with Title layout table to the InfoPath form.
- Split the second row of the table into 3 columns.
- 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.
- Rename the first Date Picker control to startDate, the second Date Picker control to endDate, and the Text Box control to dateDiff.
- 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.
- Modify the table and controls to resemble the following figure:
Figure 2. The InfoPath form in design mode..
- 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.
- Select the Tools > Programming > Microsoft Script Editor menu item to open the Microsoft Script Editor.
- 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.
dtRetVal = Null
If Trim(ISODateString) <> "" Then
dtRetVal = DateSerial(Mid(ISODateString, 1, 4), _
Mid(ISODateString, 6, 2), Mid(ISODateString, 9, 2))
ISODateStringToVBDate = dtRetVal
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.
'Retrieve the ISO date from the InfoPath date fields
strStartDate = XDocument.DOM.selectSingleNode _
strEndDate = XDocument.DOM.selectSingleNode _
'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 _
Select Case LCase(strDateInterval)
'Difference between dates in years
intDateDiff = DateDiff("yyyy", dtStart, dtEnd)
'Difference between dates in months
intDateDiff = DateDiff("m", dtStart, dtEnd)
'Difference between dates in days
intDateDiff = DateDiff("d", dtStart, dtEnd)
'Difference between dates in hours
intDateDiff = DateDiff("h", dtStart, dtEnd)
'Difference between dates in minutes
intDateDiff = DateDiff("n", dtStart, dtEnd)
'Difference between dates in seconds
intDateDiff = DateDiff("s", dtStart, dtEnd)
'If interval is not recognized so use difference in days
intDateDiff = DateDiff("d", dtStart, dtEnd)
'Set the value of the dateDiff field
("/my:myFields/my:dateDiff").text = intDateDiff
- 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:
- Modify the code for this event handler by adding an extra line of code as follows:
If eventObj.IsUndoRedo Then
- Switch back to Microsoft InfoPath and click on OK to close each open dialog box.
- Repeat steps 11 through 13 for the endDate field.
- Go to the Date Source pane and double-click on the dateInterval field to bring up its properties.
- 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
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).
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:
- Date and time basics in Microsoft Office InfoPath
- Calculating work days between two date pickers in InfoPath using VBScript
- Calculating date and time differences in InfoPath using VBScript
- Calculating a person's age given a date of birth using rules and formulas in InfoPath
- Calculate work days excluding holidays in InfoPath 2010 using SharePoint 2010 and Excel Services