3

I am trying to calculate due dates based on the description of the frequency in another field on my form. So I have 3 fields , "Frequency" which contain the description, "Annually", "Semi-annually", "Quarterly".

Then I have "Last Date" where there is a date that someone enters. and then "Due Date". I would want the first day of the selected month be the calculated due date to be in that field.

I have tried to use the "Rules" and set the condition to , when "Frequency" is equal to "Annually" then I did the set field's value, I selected the "Due Date". For the formula I wrote addDays("Last Date", 365). I was wondering if there is a way to select it to be the first day of the month?

Thank you!

adit123
  • 117
  • 2
  • 20

2 Answers2

2

Sure, use the concat/substring functions and format it like a date:

concat(substring(addDays("Last Date", 365), 1, 4), "-", substring(addDays("Last Date", 365), 6, 2), "-01")

user2051770
  • 684
  • 7
  • 18
  • This formula would be placed in when I set a field's value and I enter the formula correct? – adit123 Jan 15 '15 at 18:21
  • When I tried the formula it did work however, thank you for that!However it is returning in the format "2016-01-01" How can i change to be 01/01/2016? Sorry I am fairly new to infopath. In addition, say if i make a mistake. and change the date the field will not update. i have to go to the "Frequency" dropdown again and reclick it. also even after formatting , i am still returning the red dotted line around the box. – adit123 Jan 15 '15 at 18:24
  • mm/dd/yyyy format: concat(substring(addDays("Last Date", 365), 6, 2),"/01/", substring(addDays("Last Date", 365), 1, 4)) – user2051770 Jan 15 '15 at 19:34
  • 1
    As for it not updating, it sounds like your rule is on the "Frequency" dropdown when you really want it on the "Last Date" dropdown? Try that and see if it works as you're wanting. – user2051770 Jan 15 '15 at 19:35
  • Thank you! However I am still receiving the red dashes around the "Due Date" text. Why is this still showing up even though I formatted the "Due Date" box as a date. – adit123 Jan 15 '15 at 19:38
  • It was on "Frequency" and I tried the rules on the "Last Date" and Now the field for "Due Date" is not updating at all :( – adit123 Jan 15 '15 at 19:41
  • So your users would 1. select a frequency, 2. select the Last Date, 3. Due Date gets auto populated to the first day of the month? So if you add a rule to "Last Date" with the condition that "Frequency" = Annually, then set the action to update the "Due Date" to the formula I posted above. If done in this order it should work. Is this how you have it set up? – user2051770 Jan 15 '15 at 20:11
  • The thing is , i tried it in that order ,but the "Due Date" would not be updated. However , when the rules are all on "Frequency" ,and my order is that I select the 1."Last Date" , then select the 2."Frequency and then the "Due Date" is calculated .and say if someone made a mistake, in selecting the frequency they choose another one it is automatically update with that order and the Rules all being in "Frequency" . For some reason I am still receiving an error with the red dashes around the box. If those red dashes are around the box I cannot submit it , correct? – adit123 Jan 15 '15 at 20:19
  • Also I tried to match the format under the properties and matched it with the "Due Date" that's being calculated and I am still receiving the red dashes .I cannot pin point my problem. This is weird. Also I tried it in your order and having the rules on "Last Date" but I was still receiving the red dashes . – adit123 Jan 15 '15 at 20:20
  • In a text box, I get no error, but I would rather have it in a date box because some people might have different frequencies and they have to calculate it from there. I am also going to have a custom approval workflow set up, so when the due date approaches , the approval workflow gets sent out. that's why i would rather have it in a date entry box. – adit123 Jan 15 '15 at 20:25
  • I am still not sure what I am doing wrong with this? It seems simple , but I just can't configure the date formatting for the "Due Date" box. Is there any way to ignore this and still submit it so that it still shows? – adit123 Jan 16 '15 at 15:07
  • Well the red line is appearing because its a required field and the entered value isnt matching up for some reason. You can try making the date field not required and see if it submits ok. If it does and you still need to validate that the field isn't blank, you can use a rule on submit to check if the date field is blank or not. – user2051770 Jan 16 '15 at 16:09
1

I was able to fix the error that I was receiving after the value was being calculated. Within the new text box I created, I changed the control to date. Then as a default value, I used this formula msxsl:format-date(Last Date, "MM/dd/yyy"). And the function to use the calendar was there as well! Everything works now! Thank you @user2051770 for guiding me! Much appreciated!

adit123
  • 117
  • 2
  • 20