General

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

Hilary Stoupa — Thu, 20 Apr 2023 23:36:05 GMT

See attached. You had an error in the conditions on one of your fields - you'd typed in xdXDate instead of xdDate. This condition error was preventing the rules from stopping correctly.

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

yourluckycharm — Thu, 20 Apr 2023 15:45:52 GMT

Hi Hilary, Here's the form that i made for the calculation.

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

Hilary Stoupa — Thu, 20 Apr 2023 14:35:57 GMT

I'd need to see your form to find the issue. You can attach it under a reply on the Options tab.

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

yourluckycharm — Thu, 20 Apr 2023 12:57:19 GMT

Hi Hilary, Thank you so much for your reply and help. i already implemented all of the rules and xml file. but every time i try to do preview, it got error like this : Some rules were not applied with details : An error occured in the form's rules or code. The number of rules actions or the number of calls to the OnAfterChange event for single update in the data exceeded the maximum limit. The rules or code may be causing an infinite loop. To prevent this, ensure that the rule action or even handler does not update the data which causes the same rule action or even handler to execute. i already try to make a new form at it still got error like this one, i'm using infoPath 2013. Really looking forward for your reply. Thanks!

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

Hilary Stoupa — Wed, 19 Apr 2023 14:04:26 GMT

You can find an outline of logic for this here: https://social.msdn.microsoft.com/Forums/en-US/b72c47ad-4f83-49db-8372-3e336c2e1cde/infopath-rule-for-calculating-business-days?forum=sharepointcustomizationlegacy and I am also attaching a sample form with this logic implemented that came from this post: https://www.infopathdev.com/forums/p/40463/125295.aspx#125295

To take a look at the sample form, right click the link in the post header and save the file locally. Right click the saved file and select Design to open in design mode. The logic in that form is in the repeating table.

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

yourluckycharm — Wed, 19 Apr 2023 10:49:41 GMT

Hi are you guys have any ideas from this source for calculating 5 bussiness day? thanks for your help :)

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

Travisty2520 — Mon, 30 Jul 2018 14:57:17 GMT

Hello,

I am using two Date/Time fields to mark the start and end of a brake in out IT. I want the rules formulae to work out the difference between them as a mean time to repair for reporting currently using an spreadsheet but feel this is more progressive and can then add in other functions off this.

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

InfoPathinator — Thu, 31 May 2018 17:29:03 GMT

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:

Import the first worksheet of the above Excel file as a new list in SharePoint.
Add a receive data connection to this list from your InfoPath form.
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"), "-", "")))
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.)
Repeat steps 3 & 4for the second date.
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.

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

Paul-Emile — Fri, 14 Aug 2015 14:21:48 GMT

Hi,

This code is working well until I pick dates that are in two different months. When I pick start date = 2015-08-01 and end date = 2015-09-05, it tells me that there are 4 days separating these dates while in fact there are 34. Any suggestions as how to fix this problem?

Thanks

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

Michael Campbell — Mon, 13 Jul 2015 01:52:38 GMT

Thanks you. I am not a coder and this works perfrectly for me!!!

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

magdalena.deleva — Fri, 11 Apr 2014 07:12:44 GMT

Hi, when using this formula to calculate difference between dates, example 01/03/2013 to 03/03/2014 formula calculates 2 days, acctually difference is + days.

Can you please advice where add +1 day in the formula or to change the logic of calculation?

Thank you

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

Judyy — Wed, 26 Mar 2014 20:44:16 GMT

Will this solution work for InfoPath 2010? I have tried it with no luck :(

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

jweller — Wed, 18 Sep 2013 20:29:34 GMT

This works great, but how can one remove Saturday's and Sunday's from the calculations? Also, are there any tricks to including bank calendar holidays?

Jason

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

sengigi — Sun, 03 Mar 2013 22:36:56 GMT

I have got a different scenario - I would like to point everything to 31/12/<year+1>. Example: if I select 03/04/2013, I would like the next date to return 31/12/2014.

Any kind souls like to throw in suggestion

Re: Help please-Solve: Number of days = (todays date - date initiated) ????

earlkmi — Wed, 11 Jul 2012 19:41:47 GMT

Appreciate your help

I was able to find a product that I used to resolve the "delta" = the difference between dates.

The link is listed: http://www.bizsupportonline.net/infopath2007/calculate-date-difference-infopath-rules-formulas.htm

Calculate the difference between two date picker controls in InfoPath using rules and formulas - no code!

by S.Y.M. Wong-A-Ton | Applies to: InfoPath 2007

Use rules, conditions, and the number(), floor(), and substring() functions in formulas to calculate the difference between two date picker controls in InfoPath.

Problem

You have a form template with two InfoPath date picker controls and you would like to calculate the difference between the two date picker controls without writing code.

Solution

Use rules, conditions, and the number(), floor(), and substring() functions in formulas to calculate the difference between two date picker controls in InfoPath.

Discussion

You can accomplish this functionality as follows:

Design an InfoPath form template as shown in figure 1 with two Date Picker controls named startDate and endDate, and one Text Box control named difference.

Figure 1. InfoPath form template in Design mode.

The Main data source of the InfoPath form template should resemble the following figure:

Figure 2. The Main data source of the InfoPath form template.
Add the following Rule to the startDate field:
Action: Set a field's value
Field: difference
Value:
(number(substring(../my:endDate, 9, 2)) + floor((153 * (number(substring(../my:endDate, 6, 2)) + 12 * (floor((14 - number(substring(../my:endDate, 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) * 365 + floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 4) - floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 100) + floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 400) - 32045) - (number(substring(., 9, 2)) + floor((153 * (number(substring(., 6, 2)) + 12 * (floor((14 - number(substring(., 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) * 365 + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 4) - floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 100) + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 400) - 32045)
with the following Conditions on the Rule:
startDate is not blank and
endDate is not blank
Add a second Rule to the startDate field with the following settings:
Action: Set a field's value
Field: difference
Value: 0

with the following Conditions on the Rule:
startDate is blank or
endDate is blank
Add the following Rule to the endDate field:
Action: Set a field's value
Field: difference
Value:
(number(substring(., 9, 2)) + floor((153 * (number(substring(., 6, 2)) + 12 * (floor((14 - number(substring(., 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) * 365 + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 4) - floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 100) + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 400) - 32045) - (number(substring(../my:startDate, 9, 2)) + floor((153 * (number(substring(../my:startDate, 6, 2)) + 12 * (floor((14 - number(substring(../my:startDate, 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) * 365 + floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 4) - floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 100) + floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 400) - 32045)
with the following Conditions on the Rule:
startDate is not blank and
endDate is not blank
Add a second Rule to the endDate field with the following settings:
Action: Set a field's value
Field: difference
Value: 0

with the following Conditions on the Rule:
startDate is blank or
endDate is blank
Add the following Rule to the difference field:
Action: Set a field's value
Field: .
Value: 0

with the following Condition on the Rule:
difference does not match pattern Custom Pattern: -{0,1}\d+

You should now have a fully functional InfoPath form that will calculate the difference between the dates soon after you have entered valid dates. This solution also works for InfoPath 2003 form templates and InfoPath 2007 browser-enabled form templates.