How to insert th into a date field

Is it possible to enter th/nd/rd/st into the date field on a report?

This was the question recently asked of me by a client.

Unfortunately there isn’t a specific date format you can use to give you this information. However, with the use of a number of functions, you can build up a formula to achieve the desired result.

Lets break this down into small steps:

Step 1:

Let say we start with a date field with the following value: 09/Jul/2013 (dd/Mmm/yyyy).

The day part of this field will determine the text to append the day part of the date. There are four potential apend options depending on the day selected:

Day Number – Text To Append
1 – st
2 – nd
3 – rd
4 – th
5 – th
6 – th
7 – th
8 – th
9 – th
10 – th
11 – th
12 – th
13 – th
14 – th
15 – th
16 – th
17 – th
18 – th
19 – th
20 – th
21 – st
22 – nd
23 – rd
24 -th
25 – th
26 – th
27 – th
28 – th
29 – th
30 – th
31 – st

 As we want to query the day portion of the date, we need to use the Substr() function. This is used to bring back the first two characters of the date from a start position of one. But note: As our input value is a date field, we need to convert the date to a string. We do this using the FormatDate() function.

We end up with the following formula:

=Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2)

which returns 09

Step 2:

Now that we have the day portion, we want to identify which value to append. There are four possible values. Days 1 and 31 will have ‘st’ appended. Day 2 will have ‘nd’ appended. Day 3 will have ‘rd’ appended. And every other value will have ‘st’ appended.

We can create this formula by building on the previous statement and using the If() function. In our example we have to use a nested If() statement to evaluate three conditions. If the test doesn’t match any of the three conditions, it will default to a fourth option:

=If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“1″;”01″;”21″;”31″);”st”;If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“2″;”02″;”22″);”nd”;
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“3″;”03″;”23″);”rd”;”th”)))

You will notice that as well as checking for any days prefixed with a zero, I am also checking for the same value without a preceeding zero.

Running the above formula for 09/Jul/2013 will return the result: th as it is the 9th today.

Step 3:

We then break out the other components of the date field to extract the month and year parts. We will use these formulas shortly:

=Substr(FormatDate(CurrentDate();”dd/Mmm/yyyy”);4;3)

This returns Jul.

Step 4:

=Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);7;4)

This returns 2013.

We now have all the components we need to generate a date string which incorporates the th/nd/rd/st text.

Step 5:

Here we put it all together using several Concatenation() statements:

=Concatenation(Concatenation(Concatenation(Concatenation(Concatenation(Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2);
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“1″;”01″;”21″;”31″);”st”;
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“2″;”02″;”22″);”nd”;
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“3″;”03″;”23″);”rd”;”th”))));
” “);Substr(FormatDate(CurrentDate();”dd/Mmm/yyyy”);4;3));” “);
Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);7;4))

You will notice that I have included spaces in between the individual date components. Without this all the data will be bunched up and won’t read nicely.

We end up with the following result:

09th Jul 2013

Bonus:

The icing on the cake is the removal of the leading zero from the day part of the date. We can acheive this by performing a check on the date field using an If() statement. If the day part of teh field starts with a zero, we start our substr() statement at position 2 and return 1 character. Otherwise we start in position 1 and return 2 characters:

=Concatenation(Concatenation(Concatenation(Concatenation(Concatenation(If(Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;1)=”0″;Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);2;1);Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2));
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“1″;”01″;”21″;”31″);”st”;
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“2″;”02″;”22″);”nd”;
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“3″;”03″;”23″);”rd”;”th”))));
” “);Substr(FormatDate(CurrentDate();”dd/Mmm/yyyy”);4;3));” “);
Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);7;4))

This gives us the following result:

9th July 2013

Bear in mind that adding the th/nd/rd/st text means this field is now a string an not a date. The effect of this is that you won’t be able to use any Date specific functions on this new field. You can overcome this by performing any date calculations prior to appending the text.

So while not the most elegant of solutions, it is perfectly possible to achieve. Does anyone know a more elegant solution?