If you have the unfortunate task of doing some date calculations within InfoPath, you may have already found out the fun news: InfoPath only gives you TWO functions dealing with date and time: addDays (which lets you add a certain number of days to a date) or addSeconds (which lets you add seconds to a dateTime field). This can be a little difficult when you intend on doing some calculations like adding a month to a specific date or subtracting a few days from a date to determine another day. Here's a few ways you can do date calculations:
1. Use addDays - the addDays function looks like this: addDays(insert_a_date_field_here,"type_the_number_of_days_to_add_here"). An example would look like: addDays(StartDate,"1") and this would add a single day to the Start Date field. Key thing to note - you can add negative numbers here to subtract a certain number of days. This can be useful in conditional formatting or data validation for greater than/less than comparisons (e.g. If DueDate field is less than [addDays(StartDate, "10")] then give the error message "Your due date is too soon; please set it for more than 10 days after the start date" in the data validation tooltip.
2. Use addSeconds in the same manner as addDays - this can be done for time issues like turning an item in by 5pm on a certain day. The reason this might be necessary is that, often, when calculating dates, the time assumed for a day is 12:00 AM on that day...so if you want a realistic time like 8am, you will need to use the addSeconds to give you a few hours...you'll just have to use a calculator to figure out how many seconds to add for so many hours/minutes.
3. Adding a month or year to a date: this is tricky but doable without code. What you have to do is a little behind-the-scenes work with the date field. Here's the steps:
Have your two date fields, the first one is where the user picks a day (we'll call it UserDate), the second one is for that day plus 2 months (DatePlus2).- Create 4 text fields: temporaryDate, month, day, and year (you'll hide all these but I'm showing you them in the example)
- Use a rule to set the temporary date to the value of the UserDate.
- You'll notice that the format that it sets that field in looks like this: YYYY-MM-DD
Now you get to use the dreaded substring-before and substring-after functions to pull out the year, month, and days. The idea of substring is that a "string" is a bunch of letters and numbers (like a sentence, word, whatever) and that you are getting a portion or less than the whole thing (like subtracting out a certain part of the string...substring). There are different types of substring-ing like looking before or after a certain symbol (like a slash or dash in our case) or substring a certain number of letters in (e.g. you have a Social Security number field that always starts with SS#...you could say you want to substring starting at the 4th letter and get the next 11 characters to get all the numbers and dashes for a social security number).- Use a rule on the temporaryDate field to set the YEAR field to the following formula: substring-before((click Insert Field and choose temporaryDate), "-")...this will get all the characters before the first dash that it finds (so all the year numbers).
- Add another action to the rule on temporaryDate to set the MONTH field as the following formula: substring-before(substring-after((insert temporaryDate here), "-"),"-") This looks weird, but what it's going to do is substring after the dash (which grabs the MM-DD) and then substrings before the dash of what you grabbed (so it grabs MM since it's the only thing before the dash).
- Add another action to the rule on temporaryDate to set the DAY field as substring-after(substring-after((insert temporaryDate here), "-"),"-") This looks very similar to the Month one but it substrings after the first dash (so MM-DD is what we have) and then it substrings AFTER the dash this time (so it gets DD)
- Phew, almost done, you've now got the numbers in the month, day, and year fields for your day...all you need to do now is add to the month, day, or year field. The only problem is this: InfoPath thinks these fields are TEXT fields, not numbers, so if you try to just add 1 or 2 to the Month field, it's gonna laugh at you (because, as far as it knows, you're trying to add 2 to AA and it thinks you don't know how math works...seriously, it'll laugh at you). So, here's what you do:
- Add another action to the rule on temporaryDate to set the MONTH field to this formula: value((insert MONTH field here))+2 (or however many months you want to add to that month field). The value function will tell infopath that what you have in the MONTH field is really a number - so you can then do some math with it like adding a few months. The only hard part here is when you get toward the end of the year...you'll need to have another rule that checks to see if the month is greater than 12 and, if it is, subtract 12 from it but also add 1 to the year. Again, that's only if you are adding months...if you add to the year, then you won't have to worry about figuring out the month :)
- The final step: add another action to the rule for temporaryDate to set the DatePlus2 field to this formula: concat((insert YEAR field),"-",(insert MONTH field),"-",(insert DAY field)).
- Below are a couple of images showing the 3 rules I used here to first set the calculated date, second and third check to see if the month is over 12 and do different things based on what the number is. The reason I had 2 rules for that is that, if you subtract 12 from 13, you get 1...which is correct; however, InfoPath will want you to have "01" in the Month field instead of "1"...so I had to change what I say when I'm setting the calculated date field
