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.

ADVERTISEMENTS

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.

 
 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