Extracting day, month, and year numbers from dates in InfoPath

Filed under: InfoPath 2007

Learn how to use the substring() and number() functions in InfoPath to extract the day, month, and year numbers from an InfoPath date string.


InfoPath dates are stored in the format YYYY-MM-DD as already discussed in Date and time basics in Microsoft Office InfoPath.

The beauty of this format is that you can easily use InfoPath functions, such as substring() and number() to extract the numbers for the day, month, and year from a date.

InfoPath also has substring-before and substring-after functions you can use to split text strings in InfoPath, but they are less effective for extracting the day and month numbers from a date or a date and time string. However, they are perfect to use with date and time pickers to split a date string from a time string.

To extract the day number of a date, use

number(substring(field1, 9, 2))

To extract the month number of a date, use

number(substring(field1, 6, 2))

To extract the year number of a date, use

number(substring(field1, 1, 4))

where field1 represents an InfoPath date picker control on a form template.

Once you have the number, you can do very neat things such as use the number for the month in date together with rules, filters, and a data connection to retrieve the name for the month.

The date format used by InfoPath also lends itself well to comparing dates. You can for example easily use this date format together with data validation rules to check whether a start date falls before an end date.

For more InfoPath solutions that make use of dates, see Use Date Picker Controls in InfoPath.


Related Posts


Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services