Date Calculations in InfoPath

The SP1 update of InfoPath 2003 added calculation support – the value of a node can be set to the result of an XPath expression. This makes it possible to avoid writing code (script or managed) in many InfoPath forms. Date calculations, however, still require knuckling down and writing old fashioned procedural code.

InfoPath stores dates according to the W3C XML Schema standard, which in turn uses a variant of ISO 8601 dates. The XPath expression language, however, has no special support for date types – just strings, numbers, Booleans, node-sets and fragments. This means that while you can manipulate dates as strings – you can’t do calculations with them.

Before we dive into some sample code, though, a few notes:

You can do date comparisons with XPath! The date format is “yyyy-mm-dd” – always 4-2-2 – which means you can do lexical (“string”) comparisons on two dates and determine ordering and equality.

As a guiding rule, you should be as paranoid with date calculations as you are with financial calculations. Identify and test your edge cases thoroughly, and make sure your code matches cultural interpretations, not code convenience. For example, if you compare two dates the context and desired result matters. The relationship between a duration in days and an age in years is not simply 1/365 (or 1/365.25, or … ) – the convention for age in most cultures is “has the person had a birthday yet?” so you’d better make sure the code matches. Who wants to miss their birthday?

A good rule of software development is that if you have to think too much about a problem you’re writing too much code, and the more code you write the more likely you are to have bugs. So the moral of this story is: make someone else do all the work.

The general pattern for dealing with date calculations in InfoPath is to use an existing library. The two handy libraries for this are the Windows Scripting engine and the .NET Framework. Since we have a lot of script examples on this blog let’s use .NET this time.

The .NET Framework has a DateTime struct type and if you look in MSDN you’ll find it has plenty of methods and operator overloads for doing calculations such as adding days and computing TimeSpans. Looks good – I bet the .NET people know what they’re doing.

So basically we just want to convert an XML date into a DateTime, do some stuff with it, then convert back.

Here are the functions you need:

       private static DateTime Iso8601ToDate( string iso8601Date )

       {

              if( iso8601Date == null )

                     throw new ArgumentNullException( "iso8601Date" );

              return DateTime.ParseExact( iso8601Date, "yyyy-MM-dd", null );

       }

       private static string DateToIso8601( DateTime dateTime )

       {

              return dateTime.ToString( "yyyy-MM-dd" );

       }

Wow – after that preamble I bet that was a bit of a let down!

Now let’s use it. I built a simple calculation form that looks like this:

The button handlers look like this:

       [InfoPathEventHandler(MatchPath="date1_add1", EventType=InfoPathEventType.OnClick)]

       public void date1_add1_OnClick(DocActionEvent e)

       {

              IXMLDOMNode dateNode = thisXDocument.DOM.selectSingleNode( "/my:myFields/my:Date1" );

              try

              {

                     DateTime dt = Iso8601ToDate( dateNode.text );

                     dt = dt.AddDays( 1 );

                     dateNode.text = DateToIso8601( dt );

              }

              catch( FormatException ) {}

       }

Then I added OnAfterChange handlers for the date fields which call a sync method:

       [InfoPathEventHandler(MatchPath="/my:myFields/my:Date2", EventType=InfoPathEventType.OnAfterChange)]

       public void Date2_OnAfterChange(DataDOMEvent e)

       {

              if (e.IsUndoRedo)

                     return;

              SyncDifference();

       }

       private void SyncDifference()

       {

              IXMLDOMNode date1Node = thisXDocument.DOM.selectSingleNode( "/my:myFields/my:Date1" );

              IXMLDOMNode date2Node = thisXDocument.DOM.selectSingleNode( "/my:myFields/my:Date2" );

              IXMLDOMNode diffNode = thisXDocument.DOM.selectSingleNode( "/my:myFields/my:Difference" );

              IXMLDOMNode ageNode = thisXDocument.DOM.selectSingleNode( "/my:myFields/my:Age" );

              if( date1Node != null && date2Node != null && diffNode != null )

              {

                     try

                     {

                           DateTime dt1 = Iso8601ToDate( date1Node.text );

                           DateTime dt2 = Iso8601ToDate( date2Node.text );

                           TimeSpan ts = dt2 - dt1;

                           diffNode.text = ts.Days.ToString();

                     }

                     catch( FormatException ) {}

              }

       }

You might notice that this is computing the difference in days. The TimeSpan structure represents an interval of time, and days are the maximum granularity that a pure duration can have – month and year durations require a fixed point in time to calculate from;  even weeks can be ambiguous – is that whole weeks or calendar-weeks-spanned? And whose calendar in the first place?

So how do you go from two DateTime structures to an age? The old fashioned way – “have I had a birthday yet this year?”

       int ageInYears = dt2.Year - dt1.Year;

       if( ( dt2.Month < dt1.Month ) ||

       ( dt2.Month == dt1.Month && dt2.Day < dt1.Day ) )

       {

              ageInYears--;

       }

       ageNode.text = ageInYears.ToString();

Time calculations are even more fun. No-one mention leap seconds and we’ll get by just fine.

(Update 1/25/05 @ 11:30 AM PST - having a problem uploading the screenshot to our images site. We'll fix that ASAP.)

(Update 1/25/05 @ 11:50 AM PST - Fixed!)