Calculating the difference in dates on InfoPath forms

Calculating the difference between two dates, as in a person’s age at a particular point in time, seems to be a pretty common request among people developing InfoPath forms for use in SharePoint.  In my case I followed the advice in Alec Pojidaev’s Blog but it did not seem to work for me, giving an age that was too high.

So I have now taken inspiration from Villeroy’s post on the OpenOffice.Org forums, Re: [Solved] Replacing DATEDIF in an Excel equation?, where he presents a generic method of calculating ages in spreadsheets using IF statements.  The logic behind the approach, for years at least, is pretty obvious and can be adapted to InfoPath, even though there is no IF statement as such.

How it works

The solution also takes advantage of several InfoPath features

  • the fixed date format in InfoPath allows you to extract the day, month and year portions using the substring function.
  • rules are applied in order so you can simulate the IF statement logic from the spreadsheet formula
  • conditions on rules can include expressions (at the bottom of the list of fields) again helping to implement the IF logic

The Fields

There are 3 fields in InfoPath: DoB (date of birth), DateOfTest, and Age

Age is a read only field

DoB is a required field

DateOfTest is also required and is the field with the rules attached in this example (not really the best idea see below)

The Rules

This solution uses three rules to simulate the IF statements in Villeroy’s solution.  They are applied in order, but only one is ever run because of the conditions

Rule 1,  Month is less and Age is blank

Condition:

  • Age is blank AND
  • DoB is not blank AND
  • The expression substring(., 6, 2) < substring(../my:DoB, 6, 2)
    • substring(., 6, 2) extracts the Month as 2 digits from DateOfTest
    • substring(../my:DoB, 6, 2) extracts the Month as 2 digits from DoB   (your XPath may vary)

Action:

  • Set field Age to
  • substring(., 1, 4) – substring(DoB, 1, 4) – 1
    • substring(., 1, 4) extracts the Year as 4 digits from DateOfTest
    • substring(DoB, 1, 4) extracts the Year as 4 digits from DoB
    • -1 because we have not reached our client’s birthday yet

Rule 2,  Month is the same, the Day of Month is less and Age is blank

Condition:

  • Age is blank AND
  • DoB is not blank AND
  • The expression substring(., 6, 2) = substring(../my:DoB, 6, 2) AND
    • i.e. both dates have the same month
  • The expression substring(., 9, 2) < substring(../my:DoB, 9, 2)
    • i.e. the day of the month for DateOfTest is less than the day of the month for DoB

Action:

  • Set field Age to
  • substring(., 1, 4) – substring(DoB, 1, 4) – 1
    • i.e. the same as in Rule 1

Rule 3,  Month is greater and Age is blank (i.e. everything else)

Condition:

  • Age is blank AND
  • DoB is not blank

Action:

  • Set field Age to
  • substring(., 1, 4) – substring(DoB, 1, 4)

Why we should not just have rules attached to the DateOfTest field

In this example it is just the DateOfTest field that has the rules.  This is because in this example the DateOfTest field comes later in the form and we can expect users to fill it out sequentially.

However, if there was a danger that users might complete the DateOfTest field before completing the DoB this particular approach would not work.  To overcome this limitation a similar set of rules should also be attached to the DoB field that would be triggered if the DoB field was filled in second.