General

Re: Calculating a date

magdalena.deleva — Fri, 11 Apr 2014 08:05:28 GMT

Hi Clay,

can you advise how to calculate difference between days including start date and end date? Example, start date 01/03/2014 end date 03/03/2014. Number of days should be 3.

With this solution, difference days is 2.

Thank you very much.

Re: Calculating a date

Alec Pojidaev — Tue, 30 Dec 2008 18:18:09 GMT

Codeless solution looks nice. Although looking at the code I wasn't been able to figure out if leap years were taken into account. So I had no choice but create somthing by myself.

http://alecpojidaev.wordpress.com/2008/12/30/infopath-codeless-programming-walkthrough-2/

web-based forms: OK

IP 2003: not OK

Re: Calculating a date

Jim Cantwell — Wed, 24 Dec 2008 03:12:26 GMT

You appear to be missing a trailing right-parens:

concat("CCSBO - ", max(@ID) + 1001, " - ", substring-after(now(), "T"))

Re: Calculating a date

Gayle Cole — Tue, 23 Dec 2008 17:28:11 GMT

That is what I had original done but it is saying there is an error .. please see below concat("CCSBO - ", max(@ID) + 1001, " - ", substring-after(now(), "T") What am I doing wrong?

Re: Calculating a date

Clay Fox — Tue, 23 Dec 2008 16:42:14 GMT

Sure.

If you put the now function in an expression box and look at it you can see a couple options.

I typically use the substring-after and then use the "T" since that seperates the date from the time.

substring-after(now(), "T")

You could break it down farther to eliminate the seconds if you wanted by just having the first five characters or whatever.

Re: Calculating a date

Gayle Cole — Tue, 23 Dec 2008 16:24:52 GMT

Is there a way to substring the now() function to get just the time?

Re: Calculating a date

edwardvn — Mon, 28 Apr 2008 21:01:16 GMT

I have followed this as below and hve attached my infopath file, would you be kind enough to tell me why this doesnt work? I just keep gtting a 0

Calculating date differences in InfoPath using rules and formulas - no code!by S.Y.M. Wong-A-Ton
Level: ProblemYou have an InfoPath form with two date picker fields and would like to calculate the difference between the two date fields without writing code.SolutionUse rules, conditions, and the number(), floor(), and substring() functions in formulas to calculate the difference between two date fields in InfoPath. DiscussionYou can accomplish this functionality as follows:

Design an InfoPath form 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 should resemble the following figure:

Figure 2. The Main data source of the 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 as shown in the following figure.

Figure 3. The InfoPath form in Internet Explorer.

Re: Calculating a date

Clay Fox — Mon, 28 Apr 2008 18:49:09 GMT

Hi Edward.

In InfoPath 2007 you actually have a new function called adddays, if that is all you need. Otherwise you still must use DateDiff if you want to calculate the time between dates, etc.

Searchign on datediff will give you some examples and script code.

Re: Calculating a date

edwardvn — Mon, 28 Apr 2008 16:24:39 GMT

Please would you be kind enough to show me how to do this and perhaps give me an example in nfopath 2007?

Re: Calculating a date

Shiraz Cupala — Sat, 03 Dec 2005 04:28:55 GMT

Unfortunately date calculations are not possible without code in InfoPath 2003. The following entry on the InfoPath team blog discussed the code involved.

http://blogs.msdn.com/infopath/archive/2005/01/25/360318.aspx

Shiraz Cupala
InfoPathDev

Calculating a date

Jan Jessen — Fri, 11 Nov 2005 10:02:12 GMT

Hi erveryboy,

does anybodyknow how to calculate dates without scripting or coding?

I want do add a number (of days, e.g.) to the actual date (TODAY())? Any ideas?

brgd
Jan