How to automatically append suffixes after days of the month (i.e. 1st/2nd/3rd/4th)

Problem

Contract language can vary quite a bit across different print templates, especially in terms of how a particular date field should be expressed. The $formula(dateformat()) function is very useful for handling such requirements, and can transform the date into the desired format in almost every case.

For instance, if you wanted the Contract Start Date to be displayed in the format of MONTH DAY, YEAR, you would write $formula(dateformat(“MMMM d, YYYY”,$contract_start_date)), which would yield a result like “February 15, 2018”. For more information on other potential patterns and uses, refer to http://java.sun.com/javase/6/docs/api/java/text/SimpleDateFormat.html.

In some cases however, the requirements for expressing a date field a certain way may extend beyond what a simple dateformat() function can offer, and some additional steps and formula logic may be needed.

One example of this would be the requirement to append the correct suffix at the end of the day of the month. For example, displaying the date as “15th day of February”.

The challenge with this requirement is that there are various scenarios that need to be accounted for; the system should not only know to append “st” after 1, “nd” after 2, “rd” after 3, and “th” after 4, it should also be able to make exceptions to append “th” when the day of the month is 11, 12, or 13, along with all other cases not listed above.

Solution

To save you some time, a formula solution already exists that can be quite easily used, which is shown below and can be directly inserted into your print template to yield the desired result (if you need to use this for a different field, simply find and replace the $contract_start_date with that field’s variable).

$formula(dateformat("d",$contract_start_date)+((dateformat("dd",$contract_start_date))=="11"||(dateformat("dd",$contract_start_date))=="12"||(dateformat("dd",$contract_start_date))=="13"?"th":(((replace(dateformat("dd",$contract_start_date),".*(.)","$1"))=="1"?"st":((replace(dateformat("dd",$contract_start_date),".*(.)","$1"))=="2"?"nd":((replace(dateformat("dd",$contract_start_date),".*(.)","$1"))=="3"?"rd":"th")))))+" day of "+dateformat("MMMM",$contract_start_date))

Explanation

If you’d like to understand how this formula actually works, the deconstructed logic is explained below:

$formula(dateformat("d",$contract_start_date)
This is the first part of the formula, and simply prints the day of the month without a suffix.

((dateformat("dd",$contract_start_date))=="11"||(dateformat("dd",$contract_start_date))=="12"||(dateformat("dd",$contract_start_date))=="13"?"th":(((replace(dateformat("dd",$contract_start_date),".*(.)","$1"))=="1"?"st":((replace(dateformat("dd",$contract_start_date),".*(.)","$1"))=="2"?"nd":((replace(dateformat("dd",$contract_start_date),".*(.)","$1"))=="3"?"rd":"th")))))
This is the second and most important part of this formula, and consists of four nested ternary formulas, and translates to the following logic:
IF the day of the month is “11”, “12”, or “13”, THEN display “th”,
ELSEIF the LAST character of the day of the month is “1”, THEN display “st”,
ELSEIF the LAST character of the day of the month is “2”, THEN display “nd”,
ELSEIF the LAST character of the day of the month is “3”, THEN display “rd”,
ELSEIF display “th” for all other scenarios.

+" day of "+dateformat("MMMM",$contract_start_date))
This is the third and final part of the formula, and simply inserts the text, " day of " [MONTH].

Again, the actual application of this solution is very quick and painless given that the formula is readily available (see above in bold), but it is nonetheless an interesting implementation challenge, and can be a fun exercise when learning to build formulas such as this one.