Add Date Extensions

Excel's date formats consist of many different formats that you can use to display a date. However, one format that has always been lacking in Excel and still does not exist-is the ability to display a date as 15th October 2007. You can make Excel do this if you need it.

The use of the "th" after the digits 15 is the one format Excel does not have. To make matters even worse, as far as we are aware, it is not possible to set a custom format to display the date in this manner. Although most people simply accept that this is not possible, here is a way you can accomplish it.

On a clean worksheet, starting in cell A1, make the following entries: A1=st, A2=nd, A3=rd, A4:A20=th, A21=st, A22=nd, A23=rd, A24:A30=th, A31=st.

Name this range Extensions and then right-click and select Name a Range (pre-2007, users Insert → Name → Define), and in the Names in Workbook: box, type MyToday. In the "Refers to": box, enter the following formula:

=TEXT(TODAY(),"dddd d")&INDEX(Extensions,DAY(TODAY()),1) & TEXT(TODAY( )," mmmm yyyy")

Click Add, then OK.

Now, in any cell, simply enter =MyToday, and the current date always will display with the format Thursday 16th October 2007, or whatever date it happens to be.

If you would rather not use cells on a worksheet to store date extensions (th, st, rd, nd), you can use the CHOOSE function to house them. To do this, rightclick, go to Names in Workbook (pre-2007, Insert → Name → Define), and enter the word TheDay in the Names in Workbook: box.

In the "Refers to:" box, enter this formula:

=THEDAY(TODAY( ))

Click OK. Right-click again, go back to the Names in Workbook: box and enter the word MyToday2.

In the "Refers to:" box, enter the following formula, and click Add:

=TEXT(TODAY( ),"ddddd")&IF(TheDay=31,"st",IF(TheDay=30,"th",CHOOSE(TheDay,"st","nd","rd","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","st","nd","rd","th","th","th","th","th","th")))&TEXT(TODAY( )," mmmmyyyy").

After you click OK, you can enter =MyToday2 into any cell in which you want to display this format of date.

The date returned by the use of either of these functions will not be a true numeric date as far as Excel is concerned; it will simply be a text entry, meaning you will not be able to reference the cell housing it in any formula that expects numeric data.