Microsoft InfoPath 2010
The official blog of the Microsoft InfoPath team

Date Calculations in InfoPath

Date Calculations in InfoPath

  • Comments 8

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:

 

Date Calculation Form Screenshot

 

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!)

Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
  • PingBack from http://www.kintespace.com/rasxlog/?p=621

  • It's posible to calculate date difference with rules only.

    http://alecpojidaev.wordpress.com/2008/12/30/infopath-codeless-programming-walkthrough-2/

  • PingBack from http://www.hilpers.com/1085772-berechnung-von-zeit

  • PingBack from http://www.keyongtech.com/1238824-date-calculations

  • Are you kidding me!!!!!! Why did the "TEAM" take out the AddDAte function......PUT IT BACK...how stupid was that.......

  • We're still using InfoPath 2003.  Can you show me or point me to a link where this is done in javascript.  I'm not sure about the XDocument.SelectSingleNode or if it's XDocument::SelectSingleNode.

    Please replyh to mel.pama@renesas.com

  • I used text parsing to accomplish a datediff between two dates.  Here's how I thought it through:

    starting with:  2011-04-07T09:43:04

    FullTime = substring-after(<dateTime>,"T") = 09:43:04

    Hours = substring-before(FullTime,":") = 09

    MInutes = substring-before(substring-after(FullTime,":"),":") = 43

    (Hours * 60) + Minutes= Allminutes

    Seconds = substring-after(substring-after(FullTime,":"),":")

    (Allminutes * 60) + seconds = AllSeconds

    FinalMinutes = AllSeconds/60

    So, the final formula that I pasted into the InfoPath formula box is:

    round(((((((substring-before(substring-after(<dateTime>,"T"),":")) * 60) + (substring-before(substring-after(substring-after(<dateTime>,"T"),":"),":"))) * 60) + (substring-after(substring-after(substring-after(<dateTime>,"T"),":"),":"))) / 60) - ((((((substring-before(substring-after(<dateTime>,"T"),":")) * 60) + (substring-before(substring-after(substring-after(<dateTime>,"T"),":"),":"))) * 60) + (substring-after(substring-after(substring-after(<dateTime>,"T"),":"),":"))) / 60))

    The <DateTime> references before the - sign were replaced with a reference to the later dateTime field, and the <DateTime> references after the - sign were replaced with a reference to the earlier dateTime field.

    In my scenario, I used the form open date/time (populated in a separate field by a rule run on Form Open using the now() function) and I wanted a field to show how long between form open and when the user clicked a button on the page.  When the user clicked the button, a rule put now() into a separate field.  Then the formula above provided me with an integer of the number of minutes between opening the form and when the user clicked the button.

    Hope this helps someone down the line.

  • I have an infopath form with a begin date and an end date for reporting.  I want records if dates fall into this date range.  This comparison works flawlessly until I use a year end date of 12/31/2011.  InfoPath seems to get confused at year end for some reason and leaves this record out of the result set.  I am using conditional formatting to hide this record on the Infopath form if it does not meet that condition.  It's a very simple comparison.  

    Is dateA >= dateB?

    If so, hide this record.

    Why does the last day of the year not get included in my results set?  

Page 1 of 1 (8 items)