Converting to AM/PM Time in InfoPath Formulas

A client we were working with uses InfoPath Designer 2010 to propagate a list in SharePoint. They have a view on the list on their SharePoint site, and in one field in particular, a title field, the dates being listed were coming across as 24 hour time, which did not work for them. The mission was to make that AM/PM time. Easy enough, right?

Not so fast. This title is actually a text type field with a computed default value including some additional prefix text. Essentially they want to take “Prefix2014-07-23T21:30:00” and turn it into “Prefix 2014-07-23 9:00PM”. This means SharePoint data formatting is right out.

“Surely” you think “InfoPath formulas should be able to help manage this. I mean look, there’s a bunch of functions there and everything!” You’re right, to an extent. The problem here is that InfoPath is limited in it’s formulas to using XPath. This is likely due to how the form definition is stored in XML, but is still incredibly limited.

There is no such thing as a date format in XPath. Instead, we have to rely on “conditionals” (which, if you cannot tell from the quotes, also do not exist in XPath) built out of substrings. This is known as “Becker’s method”, after Oliver Becker who originally came up with it. What follows is the entire solution, which I will break down:

It starts off pretty simple, we’re just gonna concatenate all of our text here, starting with the prefix I mentioned before.

The first substring is pretty straightforward. XPath substring works like  substring( Text, StartPoint, Length )  so it’s nothing too outlandish. In the first substring we’re taking MyDateTime, which is consistent in being something like  2014-07-23T21:30:00  and grabbing the first 10 characters for the date, which isn’t going to change.

Edit 7/30/14: If you want to change this date format to the US version (M/D/YYYY) then just replace  substring(MyDateTime, 1, 10) with  number(substring(MyDateTime, 6, 2)),"/",number(substring(MyDateTime,9,2)),"/",substring(MyDateTime,1,4) .

Finally we get to some conditional logic. Lines 6, 7, and 8 are all working towards the same goal of making the hours come out correctly. The problem we are trying to solve is making the 24 hour period come out to the correct 12 hour time. You can see we’re grabbing that time with the embedded  substring(MyDateTime, 12, 2) .

There’s actually three conditions we’re handling here:

• The edge cases of 12AM and 12PM. We cannot have them show up as zero.
• Times 1PM and greater that show up with more than 12 hours on a 24 hour clock
• The AM Hours

So how do you manage those conditions without having the ability to use substrings? You abuse the length in the function.

On each of the substring function calls in the conditional, the third length argument is allowed to be set to zero. What this means is that you can create a bit of math in the argument that effectively returns a boolean result of 1 or 0 (and then multiply that times the length of the initial string) and decide whether or not to show that particular string. Pair that up with other substring functions within a concat() , and you end up having a full on conditional string builder.

Let’s take a look at the conditions. First the one for Noon and Midnight edge cases:

There’s actually two mini-functions here. We start by acquiring the number we’re after and making sure it is not recognized as a string:  number(substring(MyDateTime, 12, 2)) . We’ll divide it by 12 and round that up to the next integer with ceiling() . This will give us 0 for midnight, 1 for any AM hours plus noon, and 2 for any PM hours.

Then we get the same number divided by 12 and round it down to the next integer with floor() . That gives us 0 for midnight to 11AM, then 1 for PM hours. Subtract this from your first function and you get 0 for the hours of noon and midnight and 1 for every other time.

We actually need the opposite of that, so I hearkened back to my discrete math days and subtracted 1 from the result, then multiplied by -1. With the boolean result in place we multiply that times the length of the string (in this case, 2 because we’re looking to show “12”) and this part of the conditional is fully functional. The hours will now show as 12 for noon and midnight.

Now let’s look at the 1PM-on length function:

This looks like a big item, but most of it is what you already know. The first large set of parenthesis is actually the inverse of the noon/midnight condition. In this case we just want it to omit those items. We AND (multiply) it to the results to check if this is a PM time and we’re good to go.

Finally the AM function:

This is again just more of the same. Here we invert the check for PM time from the last function and we’re good. The final conditionals that actually print the AM/PM suffixes are more of that still.

The real trick here was just trying to figure out the functions that could always return 1 for the condition you want and 0 for the one you don’t. In those sorts of circumstances  floor()  and  ceiling()  are your best friends.

Some Notes and Gotchas

• This is not literal XPath data of course, MyDateTime would need to be replaced with an actual path. Working in InfoPath you’ll need to actually insert the field when working on this.
• For this to actually operate, all of the text must be on a single line. It is broken up here to be a little more legible.
• You NEED spaces on either side of mathematical operands ( + – / * ). XPath thinks its part of the name of something if you don’t have them, and it won’t validate on InfoPath.
• InfoPath does have a function called format-number, which will help you if you want some trailing zeros added in. It does not, however, work in SharePoint, so if that’s what you publish to then don’t bother.