Calculating work days between two date pickers in InfoPath using VBScript

Use the DateDiff and Weekday functions in VBScript to perform date difference calculations in InfoPath that produce the amount of work days between two dates.

Problem

You have two date pickers on an InfoPath form and want to calculate and show the amount of work days between these two dates.

Solution

Use the DateDiff and Weekday functions in VBScript to perform date difference calculations in InfoPath that produce the amount of work days between two dates.

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.
6. Add another Field (element) of type True/False (boolean) to the Main data source. Name it inWorkdays and set its Default Value to TRUE.
   Your Main data source should now resemble the following figure:
   
   Figure 1. Main data source of the InfoPath form.
7. Modify the table and controls to resemble the following figure:
   
   Figure 2. The InfoPath form in design mode..
8. 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.
9. Select the Tools > Programming > Microsoft Script Editor menu item to open the Microsoft Script Editor.
10. Add the following function to convert the ISO date string that is used internally in InfoPath into a 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

11. 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 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
Dim blnInWorkdays
Dim intCurDayNo
Dim dtCurDate
Dim intWorkdaysCounter

'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

' Check whether date difference should shown in work days.
' This only works for option "d" when difference in amount of
' days is being calculated
If LCase(strDateInterval) = "d" Then

' Retrieve whether work days should be shown
blnInWorkdays = XDocument.DOM.selectSingleNode _
("/my:myFields/my:inWorkdays").text

If CBool(blnInWorkdays) = True Then

intWorkdaysCounter = 0

' Loop through all the dates and count the work days
For i = 0 To intDateDiff - 1

dtCurDate = DateAdd("d", i, dtStart)
intCurDayNo = Weekday(dtCurDate)

If intCurDayNo > 1 And intCurDayNo < 7 Then
intWorkdaysCounter = intWorkdaysCounter + 1
End If

Next

' Set the date difference field to display the amount
' of work days between the two dates
XDocument.DOM.selectSingleNode _
("/my:myFields/my:dateDiff").text = intWorkdaysCounter

End If

End If
End If

End Sub
12. 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

13. 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
14. Switch back to Microsoft InfoPath and click on OK to close each open dialog box.
15. Repeat steps 11 through 13 for the endDate field.
16. Go to the Date Source pane and double-click on the dateInterval field to bring up its properties.
17. 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).

You can use the Default Value of the inWorkdays field to control whether the date difference is calculated showing only the amount of work days (Monday, Tuesday, Wednesday, Thursday, Friday) between two dates (inWorkdays = TRUE) or whether the date difference is calculated showing all days between two dates (inWorkdays = FALSE). Please note that this setting only makes sense when the date difference is being calculated in days, that is, when the dateInterval field has a value of d. Holidays are not taken into account.

Related InfoPath Articles:

• Calculating date and time differences in InfoPath using VBScript
• Calculating date differences in InfoPath using VBScript code
• Date and time basics in Microsoft Office InfoPath