If you want to place ordinals (st, nd, rd and th) after a number in a date. You can use the CHOOSE function to do this.

Assuming A1 has a number between 1 and 31 inclusive, the following formula in B1 will display the correct ordinal.

=CHOOSE(IF(AND(A1>10,A1<14),1,MIN(RIGHT(A1)*1,4)+1),"th","st","nd","rd","th")

The CHOOSE function allows you to specify a sequence of entries in this case “th”,”st”,”nd”,”rd” and “th”. The first argument in the CHOOSE is a number that refers to one of the items in the sequence.

In our case if the number was 3 it would extract “nd” from the sequence as “nd” is the third item in the sequence.

Notice that 3 and “nd” don’t go together. That is because we started the sequence with “th”, that is explained below.

The IF function is supplying the number to extract from the sequence in the CHOOSE function. The numbers 11, 12 and 13 are exceptions to the numbering rules they all are followed by “th”.

The other numbers (remember we are only working with dates here from 1 to 31) follow the rule 0 = “th”, 1 = “st”, 2 = “nd” 3 = “rd” and all others use “th” that is the sequence used in the CHOOSE function.

The IF function handles the exceptions first using the AND function. All other numbers can use the sequence rule.

To select the first entry in the CHOOSE sequence you use the number one. Since the zero is the first in our sequence rule we need to add one to the result of the MIN function to select the correct entry.

The MIN function uses the digit on the right of the number to determine the sequence entry and compares it to 4. The result of the RIGHT function is multiplied by 1 to convert it into a number.

Because the first item in the sequence is “th” (used for numbers ending in zero) we need to add 1 to the result of the MIN function. You can’t use a zero in the CHOOSE to select an entry.

The MIN function makes sure all the numbers ending with a 4 or more will use “th” – the last “th” in the sequence.

If A1 contains a date rather than a number then you would use the following formula in cell B1

=CHOOSE(IF(AND(DAY(A1)>10,DAY(A1)<14),1,MIN(RIGHT(DAY(A1))*1,4)+1),"th","st","nd","rd","th")