CODEWITHSUNDEEP
infopathinfopath2010infopath-2007infopath-forms-services
Vijay Swaroop
Vijay Swaroop

Reputation: 17

Find difference in days between two date fields in Infopath

Could someone help me with determining the difference in days between two date fields in InfoPath forms.

Usual subtraction doesn't work with the date fields. like DateField1 - DateField2. Any code or no code solution is highly appreciated.

Upvotes: 0

Views: 5479

Answers (2)

Dan Murphy
Dan Murphy

Reputation: 51

I utilize a separate SharePoint list to help calculate the number of days between two dates in Infopath. This is so I can account for leap years and have the ability to just count work days, not all days. I update this list with new data once a year.

Here is the Excel file source for the list containing 2018-2020 data: https://1drv.ms/x/s!ApLhBloaS1wVgsUOMRrRfbekFftY9Q

Steps:

  1. Import the first worksheet of the above Excel file as a new list in Sharepoint.
  2. Add a receive data connection to this list from your InfoPath form.
  3. For the first date, create a action to convert the date to a number in YYYYMMDD format. Assuming the date is stored as DateTime, you can use this formula: floor(number(translate(substring-before(../my:endDate, "T"), "-", "")))
  4. Query the list for the number value of the first date. Store this number in a field (column) in your form. (This field does not need to show in your form.)
  5. Repeat steps 3 & 4for the second date.
  6. Subtract the first date number from the 2nd, store this in a third column.

Note: The Excel file uses the formula "NETWORKDAYS" and includes columns for weekdays, weekdays minus US Federal holidays, and weekdays minus NYSE holidays. Now you can get the number of work days between two dates using one of these columns. If you live outside the US, you could add a column to the Excel for other holidays, such as UK bank holidays.

Upvotes: 0

PL Staggs
PL Staggs

Reputation: 11

There are ways to get difference of dates in InfoPath, but they are very complex and involve writing rules and parsing the date into month/day/year. Instead, I recommend this method described elsewhere that uses Excel Services. Because Excel is excellent at calculations, it makes sense to write the calculations in Excel and call the Excel document from InfoPath (if you have SharePoint with Excel Services).

Here are 2 sets of instructions on how to set up InfoPath and Excel Services. The instructions are long and/or copyrighted so I cannot include them here, but to summarize you would set up new Data Connections in InfoPath to use web services (SOAP) open the Excel document and set the date fields based on your InfoPath date fields and retrieve the calculated value from Excel.

Calculating date differences in InfoPath using SharePoint Excel Services

InfoPath and Excel Services

It took me about an hour to get it working because I had to do some trial & error with the Trusted Location settings.

Upvotes: 0

Related Questions