How to validate the date column in Infopath form in 3 steps

By Global SharePoint in Infopath form in SharePoint, SharePoint on March 14, 2020September 14, 2022

Validate the date column in Infopath form – Infopath was one of the favorite form designer tools, but unfortunately, Microsoft will stop support for Infopath form from 2022 onwards. In this article, I will share some techniques of how we can validate the date column in the InfoPath form (SharePoint).

Table of Contents

Validate the date column in Infopath form (InfoPath data validation)

Generally, if we have column type as the date – then by default we will get a lot of validation patterns, but if we have a text type field then we have to write our validation patterns or rules. For example, we will validate the data as dd.mm.yyyy(example: 14.03.2020) let’s see how we can do it.

If we create a text field with the data type as Date (date) – then if we click on the properties of that column – click on the “Format” button, we will see the all available format from all countries. So, there won’t be an issue with the “Date (date)” type column.

Date format in column in Infopath form — Date format in the column in Infopath form

However, if we have a column with the text type, the “Format” button will be in disabled mode.

Then, how we can format this as desired format (dd.mm.yyyy) using the custom pattern and with some validation rules?

Let’s say we have “field2txt” as a text type column then if we apply the rule like

if "field2txt" does not match pattern "\d\d.\d\d.\d\d\d\d"

Then

Show the message something like - invalid date format, please enter the format with dd.mm.yyyy format.

Date format in the column in Infopath form the custom pattern

This will work fine as long as you enter the date value as dd.mm.yyyy – but if you enter the month as 20 and day as 40 – still this will consider this as a valid value because it just checks whether the day and month have the two digits number.

Then, how to validate the day and month from this date (text type) field?

InfoPath date validation (InfoPath data validation) – Infopath date format

Maybe it could be handled in many better ways – but I thought of fixing this as quickly using the below approach.

Create two columns, name “Extract DD from Date field” and “Extract MM from Date field” with the whole number date type and make these hidden fields. These will only be used for calculation purposes.

Let’s take the example of 20.15.2019 (this is an invalid date considering the dd.mm.yyyy format because the month cannot be 15).

Now we will make use of the above fields and the string substring() function.

Get the first two digits for the day (DD) from the above original date field (text type) by using the substring () function – like below and set this value to “Extract DD from Date field“

substring(field2txt; 0; 3)

Get the first two digits for the day (MM) from the above original date field (text type) by using the substring () function – like below and set this value to “Extract MM from Date field“

substring(field2txt; 4; 2)

Notes:

In the above “field2txt” is the date field (text data type) where the user will enter the date.

In the rule of the “field2txt” date column apply the validation rules:

Rule 1:

If "Extract DD from Date field" greater than 31 or less than 1

Validation message: Invalid day in the date field, please enter the day between 1 to 31. 

Rule 2:

If "Extract MM from Date field" greater than 12 or less than 1

Validation message: Invalid month in the date field, please enter the month between 1 to 12.