Issue relsoved---read solution here---PrRe: Using Date Picker input to return a whole number

earlkmi — Wed, 11 Jul 2012 12:04:04 GMT

Hello Hilary--

I was hoping you can assist me with building this process.. I have 2 parts built: todays date and date initiated: I am at a loss for part 3, the retunr value: Total number of days (number format)

I think I may be having similar issues pertaining to 'date difference' requirements.

I'm trying to have a field return the total number of days (number format-not date) from 2 separate date values: (Todays date - date initiated)

total number of days = (todays date - date inititiated)

todays date will always be greater than (after the date initiated)

Solution for my issue with dates is listed below: 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.

Re: Using Date Picker input to return a whole number

Hilary Stoupa — Fri, 20 Jun 2008 14:48:43 GMT

Hi, Vigher:

InfoPath doesn't have a date difference function, which is what you will need to get the difference between two dates. Here is a link to another post on this forum that has a code sample as well as a link to a tutorial on another site: http://www.infopathdev.com/forums/p/3604/12772.aspx#12772. Since it will require some code to get your date difference, you may want to incorporate your conditions in the code, and attach it to the Changed event for SalesDate.

Using Date Picker input to return a whole number

Vigher — Fri, 13 Jun 2008 20:37:02 GMT

I'm trying to return a whole number value and having a bit of trouble

SalesDate = Date Picker field
PermitDate = Date Picker filed

when i enter the following formula into a field called SalesAging today() - SaleDate I recieve a NaN value. I'd also like to incorporate conditions, where PermitDate = null and SalesDate != null

Any help with this would be greatly appreciated.